Forum Discussion
Top 5 by Category
I have a table that has Country, State, Transaction ID, and Amount. Each Country has multiple States and each State has multiple Transactions. I'd like to show an aggregate table that shows, for each Country, the top 5 States based on Amount. So, USA would have five rows, Canada would have five rows, China would have five rows, etc. (assuming no ties).
I have been trying everything I can think of and still haven't achieved this seemingly simple task.
Any wisdom from the Community would be very much appreciated!
Hello Anonymous
Take a look at this video from Sam McCay where he shows how to solve what you are working on.
https://www.youtube.com/watch?v=bbM7JSQqn2I
You will end up with a couple measures.
Total Amount = SUM ( Table[Amount] )
Top 5 States = VAR RankingContext = VALUES ( Table[State] ) RETURN CALCULATE( [Total Amount], TOPN ( 5, ALL ( Table[State] ), [Total Amount] ), RankingContext )You will just have to change the name of the Table in the measures.
1 Reply
- jdbuchanan71
Super User
Hello Anonymous
Take a look at this video from Sam McCay where he shows how to solve what you are working on.
https://www.youtube.com/watch?v=bbM7JSQqn2I
You will end up with a couple measures.
Total Amount = SUM ( Table[Amount] )
Top 5 States = VAR RankingContext = VALUES ( Table[State] ) RETURN CALCULATE( [Total Amount], TOPN ( 5, ALL ( Table[State] ), [Total Amount] ), RankingContext )You will just have to change the name of the Table in the measures.