Top N by different parameters

[MbProg]

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?

[Praful_Potphode]

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

[vaibhavmahajan]

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