How to return the value in one column based on the highest value in another?

[Anonymous]

Probably a really easy question but I can't seem to figure it out.

My data source looks like this.

TABLE1

ID	SITE	CUSTOMER	ITEM	SPEND
1	1	A	item1	10
...	...	...	...	...

I've made a visual in my report that looks like this:

CUSTOMER	SUM OF SPEND
A	40
B	20
C	100
D	50
E	220

I am trying to write a measure that returns the maximum spend based overall customers. So I want my output to be this.

CUSTOMER	SUM OF SPEND	MAX SPEND
A	40	220
B	20	220
C	100	220
D	50	220
E	220	220

I tried to do CALCULATE(MAX('TABLE1'[SPEND]), ALLSELECTED('TABLE1')) but this returns the highest spend line from TABLE1 not the highest sum of spend line from the visual like I want.

Anyone able to help me please?

Thank you.

[tex628 · Accepted solution]

Hi @Anonymous ,

Try this:

Measure = MAXX( SUMMARIZE( ALLSELECTED(Table1) , Table1[Customer] , "Value" , SUM(Table[Spend]) ) [Value] )

[Anonymous]

Worked perfectly thank you. I forgot the SUMMARIZE function.