Forum Discussion

MbProg's avatar
MbProg
Helper II
10 years ago

Top N by different parameters

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?

Need Help

2 Replies

  • vaibhavmahajan's avatar
    vaibhavmahajan
    Advocate I
    8 months ago

    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

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    7 months ago

    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