Forum Discussion
How to return the value in one column based on the highest value in another?
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.
Hi Anonymous ,
Try this:Measure = MAXX( SUMMARIZE( ALLSELECTED(Table1) , Table1[Customer] , "Value" , SUM(Table[Spend]) ) [Value] )
2 Replies
- tex628
Community Champion
Hi Anonymous ,
Try this:Measure = MAXX( SUMMARIZE( ALLSELECTED(Table1) , Table1[Customer] , "Value" , SUM(Table[Spend]) ) [Value] )