topic Re: Card Graph that will show top 1, 3,and 5 based on a category with a date filter in DAX Commands and Tips

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

delithyotho — Fri, 06 May 2022 06:59:48 GMT

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!

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

tamerj1 — Fri, 06 May 2022 08:01:32 GMT

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 )

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

CNENFRNL — Fri, 06 May 2022 09:32:29 GMT

Make it all dymamic

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

v-yalanwu-msft — Tue, 10 May 2022 05:40:31 GMT

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.