Consistent Ranking Measure Ignoring Page Slicers

[mbanchik]

I am trying to create a ranking measure in Power BI based on a specific variable while ensuring that the ranking remains consistent regardless of any slicers applied on the page. But the ranking changes when the slicer is applied. The ranking should remain the same irrespective of any slicers applied on the page (specifically, slicers for Category). I have tried ALL and ALLSELECTED but the rankings change when I applied the slicers. Here is the measure I used:

rank = RANKX(ALL(Table),CALCULATE(SUM(Table[Income]),REMOVEFILTERS(Table[Category])),,DESC,Dense)
When I apply the slicer, the ranking position decreases by one spot with this measure. How can I change this for it to work?

[v-kaiyue-msft]

Hi @mbanchik ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards,

Clara Gong

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

[v-kaiyue-msft]

Hi @mbanchik ,

Thanks for the reply from @samratpbi , please allow me to provide another insight:

Instead of measure, you could try using a calculated column.

Develop measure values for dynamic calculations that change based on user actions such as filtering or slicing. This way, you can use the same measure values for different visual objects and data sections.

Unlike measure values, calculated columns are not dynamic; they are calculated only once when the table is first loaded or refreshed; this occurs during a query. As a result, calculated columns provide static values that don't change based on the filter context, making them more predictable and easier to understand in some cases.

Therefore, you can try to use calculated columns to avoid the result of an expression changing because of a change in the slicer.

More detailed information can be found at the link:

Understanding Measures vs Calculated Columns in Power BI (gorilla.bi)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[samratpbi]

Hi,

If it is about only 1 slicer, then the easy way is, may use Edit Interaction option and remove the Interaction of that specific slicer on that visual where you used Ranking.

Select the slicer visual, Go to Format tab and Edit Interaction. Remove interaction on target visual.

If this helps to remove your problem, then please mark it as solution, Thanks!

[mbanchik]

Hi, when I look in the table, it keeps the same results because of the not applied slicer but when I want to put the ranking number of a specific category in a card it does not keep the same number as the table.