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

[delithyotho]

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.

Thanks in advance!

[v-yalanwu-msft · Accepted solution]

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.

[CNENFRNL]

Make it all dymamic

[tamerj1]

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 )