Help with DAX - Getting names of top 3 countries for a selected customer and week into variables

[Anonymous]

Hi All,

I am trying to create a summary card on a report.

The overall structure of the summary will be as follows:

"[31%] of the total orders were from [United States] with a total order value of [$139k] and average order value of [$100]. The 2nd highest was [Canada] with order value of [$98k]([16%]) with an average order value of [$86]."

So I want to calculate the values above in [ ] into variables and then create a DAX to concatenate everyting. These values will change with the user selecting a a Customer and the Week Number from slicers.

However, I am having trouble calculating the names of the Top 3 countries into variables.

Below is the DAX I have come up with, but it is not getting me the results.

Note: [Order Value] is a measure calulating the sum of item price * item qty

I am hoping you guys can help me to create a DAX to get the names of the countries in their respective rank variables.

CountryRed = 
VAR ClientName = SELECTEDVALUE(Client[Client Name])
VAR Week = SELECTEDVALUE('Calendar'[ISO Week of Year])
VAR CountryR1 = CONCATENATEX(TOPN(3,VALUES(Country[Country Name]),RANKX(ALL(Country[Country Name]),CALCULATETABLE(VALUES(Country[Country Name]),Client[Client Name]=ClientName,'Calendar'[ISO Week of Year]=Week),[Order Value],ASC),ASC),Country[Country Name],",",[Order Value])
VAR CountryR2 =
VAR CountryR3 =
RETURN
CountryR1

[Anonymous]

Hi @Anonymous ,

Please check if there is a problem in the rankx formula where the text type and text number are compared together.

You can refer to the following documents that may be helpful to you:

RANKX function (DAX) - DAX | Microsoft Learn

You can refer to the following documents that may be helpful to you:

Solved: Rank customer over Date table for various slicer - Microsoft Power BI Community

Solved: TOP 10 countries using DAX code - Microsoft Power BI Community

Solved: Top and Bottom 3 Ranked Countries From Selected Co... - Microsoft Power BI Community

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.