Forum Discussion
Post PartisanTop 3 categories based on count
Hi, using the table below, how can I get the top 3 categories based on the count totals please?
| Location | Category | Count |
| Birmingham | Bike | 1 |
| Birmingham | Bike | 1 |
| Birmingham | Bike | 1 |
| Birmingham | Canoe | 1 |
| Birmingham | Canoe | 1 |
| Birmingham | Kayak | 1 |
| Birmingham | Canoe | 1 |
| Birmingham | Canoe | 1 |
| Birmingham | Canoe | 1 |
| Birmingham | Kayak | 1 |
| Birmingham | Scooter | 1 |
| Birmingham | Scooter | 1 |
| Birmingham | Canoe | 1 |
| Liverpool | Scooter | 1 |
| Liverpool | Scooter | 1 |
| Liverpool | Scooter | 1 |
| Liverpool | Kayak | 1 |
| Liverpool | Kayak | 1 |
| Liverpool | Kayak | 1 |
| Liverpool | Bike | 1 |
| Liverpool | Canoe | 1 |
| Liverpool | Canoe | 1 |
| Glasgow | Kayak | 1 |
| Glasgow | Kayak | 1 |
| Glasgow | Kayak | 1 |
| Glasgow | Kayak | 1 |
| Glasgow | Canoe | 1 |
| Glasgow | Bike | 1 |
| Glasgow | Bike | 1 |
| Glasgow | Bike | 1 |
| Glasgow | Scooter | 1 |
| Glasgow | Skates | 1 |
| Glasgow | Skates | 1 |
This is how I want the top 3 table to look please:
| Location | Category | Count |
| Birmingham | Canoe | 6 |
| Birmingham | Bike | 3 |
| Birmingham | Kayak | 2 |
| Liverpool | Scooter | 3 |
| Liverpool | Kayak | 3 |
| Liverpool | Canoe | 2 |
| Glasgow | Kayak | 4 |
| Glasgow | Bike | 3 |
| Glasgow | Skates | 2 |
Thanks
Try this ...
Total = SUM(yourdata[Count])Ranker = RANKX( all(yourdata[Category]),[total],,DESC)Draw a matrix to test the Rank is ok
Add a filter to just select the top 3 using the ranker
Please click thumbs up because I hace tried to help.
The click accept solution if it works. Thank you.
Leran more about RANKX here ...
https://www.youtube.com/watch?v=eb_-i_hWSDI
7 Replies
speedrampsSuper User
Try this ...
Total = SUM(yourdata[Count])Ranker = RANKX( all(yourdata[Category]),[total],,DESC)Draw a matrix to test the Rank is ok
Add a filter to just select the top 3 using the ranker
Please click thumbs up because I hace tried to help.
The click accept solution if it works. Thank you.
Leran more about RANKX here ...
https://www.youtube.com/watch?v=eb_-i_hWSDI
johnt75You can create a measure like
Rank = RANK ( ALLSELECTED ( 'Table'[Location], 'Table'[Category] ), ORDERBY ( CALCULATE ( SUM ( 'Table'[Count] ) ), DESC ), PARTITIONBY ( 'Table'[Location] ) )add apply that as a filter to the table visual, set to show when the value is less than or equal to 3
- bhanu_gautam
Create a new table to summarize the counts by Location and Category. You can do this by using the following DAX formula:
SummaryTable =
SUMMARIZE(
'YourTable',
'YourTable'[Location],
'YourTable'[Category],
"TotalCount", SUM('YourTable'[Count])
)Add a rank column to rank the categories within each location based on the count. Use the following DAX formula:
SummaryTableWithRank =
ADDCOLUMNS(
SummaryTable,
"Rank", RANKX(
FILTER(
SummaryTable,
[Location] = EARLIER([Location])
),
[TotalCount],
,
DESC,
DENSE
)
)Finally, filter the table to show only the top 3 categories for each location. You can do this by creating a new table with the following DAX formula:
Top3Categories =
FILTER(
SummaryTableWithRank,
[Rank] <= 3
)Use the Top3Categories table to create your desired visualizations in Power BI.
- v-achippa
Community Support
Hi RichOB,
Thank you for reaching out to Microsoft Fabric Community.
Thank you speedramps, bhanu_gautam , johnt75 for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's resolved your issue? or let us know if you need any further assistance.
If any response resolved your issue, please mark it as "Accept as solution" and give a kudos if you found it helpful.Thanks and regards,
Anjan Kumar Chippa
- speedramps
Hi RichOB
I went to a lot of effort to provide an explanation and example.
Did you try my method?Please click thumbs up and the [accept as solution] buttons.
It is polite and sensible to thank helpers this way. 😀
