Skip to main content
cancel
Showing results for
Search instead 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.

Thanks in advance!

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 )``````

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors