cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Card Graph that will show top 1, 3,and 5 based on a category with a date filter

Hello,

I have a table that looks like this:

 Report Date Country Amount 09/31/2021 Country1 1000 09/31/2021 Country1 2000 09/31/2021 Country2 3000 09/31/2021 Country2 1000 09/31/2021 Country3 5000 09/31/2021 Country3 1000 09/31/2021 Country4 7000 09/31/2021 Country4 8000 09/31/2021 Country5 1000 09/31/2021 Country5 1000 07/30/2021 Country1 5000 07/30/2021 Country1 1000 07/30/2021 Country2 7000 07/30/2021 Country2 8000 07/30/2021 Country3 1000 07/30/2021 Country3 1000 07/30/2021 Country4 1000 07/30/2021 Country4 2000 07/30/2021 Country5 3000 07/30/2021 Country5 1000

 Report Date 09/31/2021 Country1 3000 Country2 4000 Country3 6000 Country4 15000 Country5 2000

I would like to create individual card graphs that will show the top 1 (Country4), 3 (Country3) and 5 Country5). I'm also using a Date Slicer to check out whos the top 5 in previous reports.

The country entries spans up to more than 20 so its important to get the top 1, 3 and 5. I thought i can get away with using the Filters on this Visual option but it only allows us to use one filter for the Country column.

1 ACCEPTED SOLUTION
Community Support

Hi, @delithyotho ;

You could create a measure.

rank = RANKX(ALLSELECTED('Table (2)'),CALCULATE( CALCULATE(SUM([Amount]),FILTER(ALLSELECTED('Table (2)'),[Country]=MAX([Country])))),,DESC,Dense)
top = "TOP1 : "& CALCULATE(MAX([Country]),FILTER('Table (2)',[rank]=1))
&UNICHAR(10)&"TOP3 : "& CALCULATE(MAX([Country]),FILTER('Table (2)',[rank]=3))
&UNICHAR(10)&"TOP5 : "& CALCULATE(MAX([Country]),FILTER('Table (2)',[rank]=5))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi, @delithyotho ;

You could create a measure.

rank = RANKX(ALLSELECTED('Table (2)'),CALCULATE( CALCULATE(SUM([Amount]),FILTER(ALLSELECTED('Table (2)'),[Country]=MAX([Country])))),,DESC,Dense)
top = "TOP1 : "& CALCULATE(MAX([Country]),FILTER('Table (2)',[rank]=1))
&UNICHAR(10)&"TOP3 : "& CALCULATE(MAX([Country]),FILTER('Table (2)',[rank]=3))
&UNICHAR(10)&"TOP5 : "& CALCULATE(MAX([Country]),FILTER('Table (2)',[rank]=5))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Champion

Make it all dymamic

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Super User

Hi @delithyotho
Here is a sample file with the solution https://we.tl/t-m7TsF2A4fU
I hope this is what you're looking for

Sales Amount = SUM ( Sales[Amount] )
Top 3 Odd Ranking =
VAR SummaryTable = SUMMARIZE ( Sales, Sales[Country], "@Amount", [Sales Amount] )
VAR RankingTable = ADDCOLUMNS ( SummaryTable, "@Ranking", RANKX ( SummaryTable, [@Amount] ) )
VAR FilteredTable = FILTER ( RankingTable, ISODD ( [@Ranking] ) )
RETURN
CONCATENATEX ( FilteredTable, [Country], UNICHAR(10), [@Ranking], ASC )