Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
85 | |
85 | |
69 | |
67 | |
63 |
User | Count |
---|---|
213 | |
124 | |
117 | |
81 | |
75 |