Forum Discussion
Stop ranking measure dynamically changing when data is sliced
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!
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])))
6 Replies
- Pragati11
Super User
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
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