Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |