How to apply the maximum and pick the "winner" in BI?

[PoorBIGuy]

Hi Fellas,

I am see this kind of table in BI:

Basically, I want to see which region generates the most revenue for each sales rep. For example, for sales rep A, it's "Central" so I hope the BI formula will return "Central"; for sales rep B, it's "east coast".

Is there a way to do it in BI?

Thanks!

[v-zhouwen-msft · Accepted solution]

Hi @vicky_ ,thanks for the quick reply, I'll add further.

Hi @PoorBIGuy ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

Measure = 
VAR _a = MAX('Table'[Sales])
VAR _b = CONCATENATEX(FILTER('Table','Table'[Sales] = _a),'Table'[Region],",")
RETURN _b

2.Final output

[v-zhouwen-msft · Accepted solution]

Hi @vicky_ ,thanks for the quick reply, I'll add further.

Hi @PoorBIGuy ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

Measure = 
VAR _a = MAX('Table'[Sales])
VAR _b = CONCATENATEX(FILTER('Table','Table'[Sales] = _a),'Table'[Region],",")
RETURN _b

2.Final output

[PoorBIGuy]

I think one thing is missing then the sales from one region should be a sum. For A, total sales from Central is 310 (150+80+80) and West is 250 (150+100) so eventually, only "Central" should return

[vicky_ · Accepted solution]

OK so then you can sum the sales per region and customer and then grab the related region by max sales. 

Region with Most Sales per Customer = CALCULATE(MAX('Table'[Region]), TOPN(1, SUMMARIZE('Table', 'Table'[Customer], 'Table'[Region], "sales", SUM('Table'[Sales])), [sales], DESC))

[vicky_]

Try the following measure:

Region with Most Sales per Customer = CALCULATE(MAX('Table'[Region]), TOPN(1, 'Table', 'Table'[Sales], DESC))

[PoorBIGuy]

Unfortunately, this doesn't work