How to get top agent from top country based on sales volume in Power BI

[Wongsc13]

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!

[barritown]

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

My YouTube vlog in English

My YouTube vlog in Russian

[Wongsc13]

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

[barritown]

@Wongsc13,

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

My YouTube vlog in English

My YouTube vlog in Russian

[Wongsc13]

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

[barritown · Accepted 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

My YouTube vlog in English

My YouTube vlog in Russian

[Wongsc13]

Hi Alexander,

You are a star! It is working perfectly now!
I am really grateful for your help in this.

Thank you,
Song