topic Top N by different parameters in DAX Commands and Tips

Top N by different parameters

MbProg — Wed, 13 Jul 2016 09:34:21 GMT

Hello,

I have a dataset with very simple data: ProductID, Region, Value, Category. I wanted to rank the Products by their Value. So I did the following:

Create Measures:

1. Total_Budget = SUM('Projects'[Value])

2. Rank = RANKX(ALLSELECTED(Projects);[Total_Budget];;DESC;Dense)

And it works. When I create a table diagram, they are ranked correctly and when I use a slicer and filter the region, then the data is refreshed correctly:

Now I want to remove the ProductID column from the diagram and ranked again by their values but by the region, so that I get the regions ranked by their values (of course it is the same Value Column as before)

But removing the ProductID column results in a different result not correctly sorted and ranked:

How can I accomplish this? What am I doing wrong?

Re: Top N by different parameters

vaibhavmahajan — Fri, 26 Dec 2025 13:25:42 GMT

Hi MbProg,

I hope you are doing well today 🙂❤️

There are two solutions you can use to fix the rank issue

Solution 1: Rank by Region explicitly

Total_Budget := SUM ( Projects[Value] )

Rank by Region := RANKX ( ALLSELECTED ( Projects[Region] ), [Total_Budget], , DESC, DENSE )

Key takeaway (important):

ALLSELECTED(Projects[Region]) → ranking list = Regions
Total_Budget → evaluates Value per Region

Ranking now matches the table granularity

Works with slicers
Works when ProductID is removed
Correct sorting & ranking

Sort: By Rank by Region (ascending) in Table / Visual

Solution 2: Dynamic Ranking

If you want dynamic behavior (rank by Product when ProductID is present, otherwise by Region):

Total_Budget := SUM ( Projects[Value] )

Dynamic Rank := IF ( ISINSCOPE ( Projects[ProductID] ), RANKX ( ALLSELECTED ( Projects[ProductID] ), [Total_Budget], , DESC, DENSE ), RANKX ( ALLSELECTED ( Projects[Region] ), [Total_Budget], , DESC, DENSE ) )

Key takeaway (important):

RANKX must always rank over the same column(s) used in the visual grouping.

If the visual shows:

Products → rank Products

Regions → rank Regions

Final Implementation Screenshot:

If this answer helped, kindly give Kudos and mark it as the Accepted Solution

to help other members find it more quickly.

Best regards,

Vaibhav Mahajan

LinkedIn: https://www.linkedin.com/in/vaibhavnmahajan

Re: Top N by different parameters

Praful_Potphode — Sat, 03 Jan 2026 14:31:49 GMT

Hi MbProg ,

try below measure:

Dynamic Rank = SWITCH( TRUE(), ISINSCOPE(Projects[ProductID]), RANKX( ALLSELECTED(Projects[ProductID]), [Total_Budget], , DESC, Dense ), ISINSCOPE(Projects[Region]), RANKX( ALLSELECTED(Projects[Region]), [Total_Budget], , DESC, Dense ), BLANK() )

Sample PBIX

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful