Power Bi Rank Function Not Staying Constant On Certain Slicers

[Anonymous]

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.

[Anonymous · Accepted solution]

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.

[rajendraongole1]

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]

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.