Forum Discussion
Show top N/Rank based on multiple dimensions
Hello,
I have a table with the format below with dimensions coming from multiple tables and sales coming from the fact table.
I would like to be able to filter the table based on the top N values of the combination of the columns. However the only way to use top N is on one dimension only.
I then tried to create a rank measure using CROSSJOIN but it is not working properly and sometimes when I see the ranking it puts a ranking of 2 for example to two rows that have completely different numbers.
My goal is to be able to create a Rank Measure for the combination of the three columns below of Country, Product, and Year without having to combine them in one column.
My Rank measure should result in the below:
| Country | Product | Sales | Year | Rank |
| US | A | 10000 | 2024 | 1 |
| ES | A | 2000 | 2024 | 6 |
| FR | B | 5000 | 2024 | 3 |
| AA | A | 1000 | 2024 | 9 |
| PT | B | 1500 | 2024 | 7 |
| US | B | 5500 | 2025 | 2 |
| ES | B | 4000 | 2025 | 4 |
| FR | A | 3500 | 2025 | 5 |
| AA | D | 800 | 2025 | 10 |
| PT | A | 1200 | 2025 | 8 |
Thanks a lot!
Hi Anonymous
If your model is something like :You can use the DAX :
rank_ = if (ISBLANK([total_sales]),blank(),RANKX(all('Table'),[total_sales],,DESC))The result :PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
3 Replies
- Ritaf1983
Super User
Anonymous
Happy to help 😊
- Ritaf1983
Hi Anonymous
If your model is something like :You can use the DAX :
rank_ = if (ISBLANK([total_sales]),blank(),RANKX(all('Table'),[total_sales],,DESC))The result :PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly