Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Power BI Gurus,
I have been trying to figure out for days on how to get the name of the top agent from the top country based on the total sales.
Table 1:
The above sample table above 'Table1' shows a simple table with 3 columns - Country, Agent and Sales.
Based on the tabulation table, Indonesia has the highest total sales volume, how do I get the top agent from Indonesia, in this example, Agent : AAA. I want to display this in a visual card in my report.
I have tried using Rankx, grouping, Topn but all did not work for me.
So I would greatly appreciate it if I can get some help for this problem.
Thank you very much in advance!
Solved! Go to Solution.
Hi @Wongsc13,
Please check the modified file and test the updated measure.
The raw data was a bit different from what you provided in your initial message so one more summarization was needed.
Best Regards,
Alexander
Hi @Wongsc13,
Here is an option:
Here is the same code in plain text:
Top Seller of Top Selling Region =
VAR _tbl = SUMMARIZE ( 'Table', [Country], "Total Sales", SUM ( 'Table'[Sales] ) )
VAR topSellingCountry = MAXX ( TOPN ( 1, _tbl, [Total Sales] ), [Country] )
RETURN MAXX ( TOPN ( 1, FILTER ( 'Table', [Country] = topSellingCountry ), [Sales] ), [Agent] )
Best Regards,
Alexander
Dear Alexandra,
Thank you so much for your help in providing DAX formula, I have tried to replicate the code in my actual report using the actual variable names as below:
TopAgentInTopCountry =
VAR _Tbl = SUMMARIZE(Combined_Import, Combined_Import[POL_CtryCode], "Sum of RT", SUM(Combined_Import[RT]))
VAR _topSellingCountry = MAXX(TOPN(1, _Tbl, [Sum of RT]), [POL_CtryCode])
RETURN
MAXX(TOPN(1, FILTER(Combined_Import, Combined_Import[POL_CtryCode] = _topSellingCountry), [Sum of RT]), Combined_Import[AGENT])
And the result is close to what I require, but the only issue is that, it does seem to return me the top agent (AAA) with the most "SUM of RT", but instead it seems to be randomly returning 1 agent name, in my case "BBB":
May I know how can I fix this issue?
Thank you once again!
Song
If you can reproduce your problem with some mocking data and share the file with me, I will be able to point out where the problem is. Without that, I can only assume that if you have only one row per each agent in your original table, you should have [RT] Instead of [Sum of RT] in the output line:
MAXX(TOPN(1, FILTER(Combined_Import, Combined_Import[POL_CtryCode] = _topSellingCountry), [Sum of RT]), Combined_Import[AGENT])
Also you can check the attached file where the measure returns the expected result and compare it with what you have.
Best Regards,
Alexander
Dear Alexander,
Thank you very much once again for your reply and creating the Power BI sample file.
However, after applying same logic as per your file, it is not getting the top agent correctly.
I have done a mock up of the Power BI report as uploaded in the the Gdrive link (can't seem to attach a file here).
https://drive.google.com/file/d/1r2HDvOnvugDjQdzRzVDBjJae2jD5Rksq/view?
In the Power BI, I have added the visual card based on the formula, and I am still getting BBB:
In the matrix table also included, the top agent by top country (Indonesia) should be AAA:
I would greatly appreciate it if you can help me shed some light on this.
Thank you very much!
Best regards,
Song
Hi @Wongsc13,
Please check the modified file and test the updated measure.
The raw data was a bit different from what you provided in your initial message so one more summarization was needed.
Best Regards,
Alexander
Hi Alexander,
You are a star! It is working perfectly now!
I am really grateful for your help in this.
Thank you,
Song