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.

Reply
delithyotho
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 DateCountryAmount
09/31/2021Country11000
09/31/2021Country12000
09/31/2021Country23000
09/31/2021Country21000
09/31/2021Country35000
09/31/2021Country31000
09/31/2021Country47000
09/31/2021Country48000
09/31/2021Country51000
09/31/2021Country51000
07/30/2021Country15000
07/30/2021Country11000
07/30/2021Country27000
07/30/2021Country28000
07/30/2021Country31000
07/30/2021Country31000
07/30/2021Country41000
07/30/2021Country42000
07/30/2021Country53000
07/30/2021Country51000

 

Report Date09/31/2021
  
Country13000
Country24000
Country36000
Country415000
Country52000

 

 

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
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1652161111726.png

vyalanwumsft_1-1652161122575.png


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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1652161111726.png

vyalanwumsft_1-1652161122575.png


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
Community Champion
Community Champion

Make it all dymamic

CNENFRNL_0-1651829502569.gif

 


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!

tamerj1
Super User
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 )

 

1.png

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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