Stop ranking measure dynamically changing when data is sliced

[Anonymous]

So as many of you will know I have a beautiful ranking measure which ranks average months active by group:

Rank: Months Active = RANKX(ALLSELECTED('ELIGIBLE_POLICY'[GROUP POLICY NAME]),CALCULATE(SUM('ELIGIBLE_POLICY_HH'[AVERAGE_MONTHS ACTIVE])))

So this is super when in an unfiltered table, but when I filter the table on GROUP POLICY NAME so I can see just one group, the rank measure changes to 1.

I know why this is happenening, but I don't what it to. I want to be able to see their rank value against all groups, irrespective of whether the data has been sliced. 

Help!

[Pragati11]

Hi @Anonymous ,

Try chnging your DAX to as follows:

Rank: Months Active = RANKX(ALL('ELIGIBLE_POLICY'[GROUP POLICY NAME]),CALCULATE(SUM('ELIGIBLE_POLICY_HH'[AVERAGE_MONTHS ACTIVE])))

Let me know how this shows the output.

Thanks,

Pragati

[Anonymous]

Thanks @Pragati11 - this works beautifully if I only apply a slicer against the field [GROUP POLICY NAME] but each group has a reference or two associated to it, which people can also use to slice the data. 

When I slice by Group reference instead of the GROUP NAME, the ranking goes to 1 still. 

Is there any way around this? 

[Pragati11]

Hi @Anonymous ,

Can you share me some screesnhots around this please?

Thanks,

Pragati

[Anonymous]

Sure @Pragati11, see attached - focus on the top row 1 HOTEL and the slicer applied.

  

 

[Pragati11 · Accepted solution]

Hi @Anonymous ,

What happens when you modify your DAX as follows:

Rank: Months Active = RANKX(ALL('ELIGIBLE_POLICY'),CALCULATE(SUM('ELIGIBLE_POLICY_HH'[AVERAGE_MONTHS ACTIVE])))

[Anonymous]

@Pragati11 it's no longer affected by the filter but it's not ranking 1 - 100, it's got some odd values, who would have been rank 1 is now rank 20, rank 2 then jumps to 317.