Forum Discussion

MbProg's avatar
MbProg
Icon for Helper II rankHelper 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('Pro...
Need Help
vaibhavmahajan's avatar
vaibhavmahajan
Icon for Advocate I rankAdvocate 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