Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Power Bi Rank Function Not Staying Constant On Certain Slicers

Hi,

 

Im having an issue with the  rank function. Currently I have a two slicers One on date and the other on Trade counterparty. The rank function works when I change the date but when I change the trade counterparty to narrow it down somehow the rank changes.

This is currently before adding the slicer, and this is the result after the slicer is applied:

Somehow Gold goes from 2nd in the first ranking to 3rd in the second ranking. 

This is my rank function:

CommRank =

CALCULATE (

    RANKX (

        ALL('DEXTR_EQ_Trades'[TRD_COUNTERPARTY]),  // Replace 'Table' and 'Column To Rank' with actual table name and column

        CALCULATE ( SUM ('DEXTR_EQ_Trades'[TRD_COMMISSION_GBP]) )  // Replace 'Table' and 'Value Column' with actual table name and column that has the values to calculate

    )

)

 

Any help would be appreciated.

 

 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous , rajendraongole1 Thank you for your prompt reply!


    Based on my testing, the Rank value will change based on the multislicer context.

     

    As a workaround, we can transform the data in Power Query, sort the TRD_COMMISSION_GBP columns in descending order, and then add index columns to make the sorting static:

     

    In this way, we can see the difference between the original rank column and the index column:

     

    Best regards,

    Joyce

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Anonymous - Can you try the below measure that the rank calculation includes all possible values of TRD_COUNTERPARTY. The ALLSELECTED function ensures that the sum of TRD_COMMISSION_GBP respects the currently selected slicers.

     

    CommRank =
    CALCULATE (
    RANKX (
    ALL ( 'DEXTR_EQ_Trades'[TRD_COUNTERPARTY] ),
    CALCULATE ( SUM ( 'DEXTR_EQ_Trades'[TRD_COMMISSION_GBP] ) ),
    ,
    DESC
    ),
    ALLSELECTED ( 'DEXTR_EQ_Trades' )
    )

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,

       

      Im looking for the rank to stay the same and not change how it is calculated.. For example as the "GOLD" is in rank 3 before i select the slicer. When I select the slicer for just GOLD then I want the rank to still be 3, I dont want the rank calculation to be affected.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous , rajendraongole1 Thank you for your prompt reply!


    Based on my testing, the Rank value will change based on the multislicer context.

     

    As a workaround, we can transform the data in Power Query, sort the TRD_COMMISSION_GBP columns in descending order, and then add index columns to make the sorting static:

     

    In this way, we can see the difference between the original rank column and the index column:

     

    Best regards,

    Joyce

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.