topic Re: Retrieve Top5 Countries Total from a table visual and have it displayed in a card visual? in DAX Commands and Tips

Retrieve Top5 Countries Total from a table visual and have it displayed in a card visual?

AndreasHermle — Mon, 12 May 2025 15:21:38 GMT

Dear Experts:

I have got a table visual as follows:

It shows the Top 5 Countries by Category Sales. along with a Ranking Column. It works just fine.

The two measures Top5Countries and Ranking are as follows (please see below). They work just fine everything is ok.
Now here comes my question: How can I retrieve The Total Sales as depicted in the Table Visual (142,171,598 €) and have that value displayed in a card visual.

Help is very much appreciated. Thank you very much in advance. Regards, Andreas

Top5Countries =

Var CountryRank =

RANKX(

FILTER(

ALLSELECTED(DimCtry),

CALCULATE(

[Total Sales],

DimProd[CategoryID] = SELECTEDVALUE(DimCat[CategoryID]) // Ensure ranking respects category context

)

CALCULATE([Total Sales]),

DESC,

DENSE

)

RETURN

IF (CountryRank <= 5, [Total Sales], BLANK())

Ranking =

VAR RankValue =

RANKX(

FILTER(

ALLSELECTED(DimCtry),

CALCULATE(

[Total Sales],

DimProd[CategoryID] IN VALUES(DimCat[CategoryID])

)

[Total Sales],

DESC,

DENSE

)

RETURN

IF(RankValue <= 5, RankValue, BLANK()) // Only keep ranks 1-5, ignore others

Re: Retrieve Top5 Countries Total from a table visual and have it displayed in a card visual?

Jihwan_Kim — Mon, 12 May 2025 17:18:16 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

In this case, one of ways is using WINDOW and RANK DAX functions in measures.

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

RANK function (DAX) - DAX | Microsoft Learn

Total sales: = SUM(sales[sales])

Top 5 countries within category: = VAR _topn = WINDOW ( 1, ABS, 5, ABS, SUMMARIZE ( ALL ( sales ), category[category], country[country] ), ORDERBY ( [Total sales:], DESC ), , PARTITIONBY ( category[category] ) ) RETURN CALCULATE ( [Total sales:], KEEPFILTERS ( _topn ) )

Ranking: = IF ( [Top 5 countries within category:], RANK ( SKIP, SUMMARIZE ( ALL ( sales ), category[category], country[country] ), ORDERBY ( [Total sales:], DESC ), , PARTITIONBY ( category[category] ) ) )

Re: Retrieve Top5 Countries Total from a table visual and have it displayed in a card visual?

v-kpoloju-msft — Tue, 13 May 2025 04:39:25 GMT

Hi AndreasHermle,

May i know has your issue been resolved? If the response provided by the community member Jihwan_Kim, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

Re: Retrieve Top5 Countries Total from a table visual and have it displayed in a card visual?

AndreasHermle — Tue, 13 May 2025 16:34:05 GMT

Wow Kim,
I am deeply impressed by your expertise. That's the way I wanted it. Thank you very much for your great help. I really appreciate it.
Best Regards, Andreas