Forum Discussion

anshusaketsingh's avatar
anshusaketsingh
Frequent Visitor
1 year ago
Solved

Unexpected Ranking Behavior When Adding Dimensions in Power BI

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 ranki...
Need Help
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

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

     

  • anshusaketsingh's avatar
    anshusaketsingh
    1 year ago

    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)
anshusaketsingh's avatar
anshusaketsingh
Frequent Visitor
1 year ago

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's avatar
Jihwan_Kim
Super User
1 year ago

Hi,

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

 

  • anshusaketsingh's avatar
    anshusaketsingh
    Frequent Visitor
    1 year ago

    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)