Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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
OK so then you can sum the sales per region and customer and then grab the related region by max sales.
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
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
OK so then you can sum the sales per region and customer and then grab the related region by max sales.
Try the following measure:
Region with Most Sales per Customer = CALCULATE(MAX('Table'[Region]), TOPN(1, 'Table', 'Table'[Sales], DESC))
Unfortunately, this doesn't work