Unexpected Ranking Behavior When Adding Dimensions in Power BI

[anshusaketsingh]

I was working with the RANK function to rank sales by Sub-Category, and it works fine when only Sub-Category is in the table. However, when I add another dimension (like a different field), the ranking behaves unexpectedly—it starts from 2 and then jumps to 4, instead of starting from 1 and ranking sequentially. I want the ranking to always reflect the individual ranking of Sub-Categories, regardless of any additional fields, with only slicers influencing the result. I’m trying to understand why this is happening and how to correct it.

Let me know if you’d like help reviewing or adjusting your DAX formula too!

Power BI Workbook 

Here are DAX 

Total Saless All Except Sub-Cat With Slicer = CALCULATE([Total Sales],ALLEXCEPT(Orders,Orders[Sub-Category],Orders[Region]))

Sub-Category Ranking =

RANKX(ALLSELECTED(Orders[Sub-Category]),[Total Saless All Except Sub-Cat With Slicer],,,DENSE)

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message, and in my opinion, please try to write the measures like the attached file.

[Jihwan_Kim]

Hi,

Please try writing a measure something like below whether it suits your requirement.

RANK function (DAX) - DAX | Microsoft Learn

Sub-Category Ranking = 
RANK (
    DENSE,
    SUMMARIZE ( ALLSELECTED ( Orders ), Orders[Sub-Category], Orders[Segment] ),
    ORDERBY ( [Total Saless All Except Sub-Cat With Slicer], DESC )
)

[anshusaketsingh]

Thanks a lot for responding to my query. The RANK function, as you mentioned, gives the desired result, but I’m still curious to understand why the RANKX function doesn’t behave as expected in my case. This is more of a learning opportunity for me than just applying a fix, so I’d really appreciate it if you could help me understand what I might be doing wrong with RANKX, and how to achieve similar results using it correctly.

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message, and in my opinion, please try to write the measures like the attached file.

[anshusaketsingh · Accepted solution]

Thanks , this really helps. The issue was never the Rankx but the underlying measure .

 

[Total Saless All Except Sub-Cat With Slicer]=CALCULATE([Total Sales], ALLSELECTED(Orders),VALUES(Orders[Sub-Category]))

Once I changed that , RankX worked like a charm without need to summarize 

Sub-Category Ranking =

RANKX( ALLSELECTED ( Orders[Sub-Category]), [Total Saless All Except Sub-Cat With Slicer],,,Dense)

[amitchandak]

@anshusaketsingh ,  can also include region in Rankx and Try

Sub-Category Ranking =
RANKX(ALLSELECTED(Orders[Sub-Category],Orders[Region]),[Total Saless All Except Sub-Cat With Slicer],,,DENSE)

[anshusaketsingh]

Your formula didn't work. Even if it did, it would make the ranking inconsistent, especially when another region dim is added to the table as region as dimension shouldn't impact ranking.