Show result on specific number

[azaterol]

Hello everyone,

I want a table to contain the same column twice. The first should only output numbers if the account = 123 is output. The second column should only display numbers from the account = 456.

Reference Nr.		Account		Value
773		123		$ 5
773		456		$ 10
773		999		$ 9
773		288		$ 100

That's what I want as a result. A column value 123 and a column 456, which only contain the amounts of the respective account. How do I do that?

This is how I imagine the result:

Reference Nr.		Customer		Value (123)		Value (456)
773		Alex		$ 5		$ 10
220		Dave		$ 2		$ 3
221		John		$ 22		$ 30
222		Doe		$ 91		$ 33

Thank you for help !

[v-tianyich-msft · Accepted solution]

Hi @azaterol ,

Based on your description I have created simple samples and you can check the results as follows:

This is not limited to your 123 or 456 rows, but the first and second rows of data

count = var _t = ADDCOLUMNS('Table',"count",RANKX(FILTER(ALL('Table'),[Reference Nr.]=EARLIER([Reference Nr.])),[Index],,ASC,Dense))
return SUMX(_t,[count])

For one = CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[count]=1))

For two = CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[count]=2))

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[lkalawski]

Hi @azaterol ,

You can do this by creating measures (but it is not best solution when you have a lot of accounts and you would like to show all as columns):

Value (123) = CALCULATE(SUM('Table'[Value]), KEEPFILTERS('Table'[Account] = 123))
Value (456) = CALCULATE(SUM('Table'[Value]), KEEPFILTERS('Table'[Account] = 456))

Or better one - use matrix and one measure:

Sum of Value = Sum('Table'[Value])

Question - what happen if you have more than one value per Account - should then be a sum or average or max?