Show top N/Rank based on multiple dimensions

[aerizk]

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!

[aerizk]

hi Rita, that works. Thanks a lot!

[Ritaf1983]

@aerizk 

Happy to help 😊

[Ritaf1983 · Accepted solution]

Hi @aerizk 
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