Forum Discussion
Change ranking to be static when filtered
Hi everyone,
I am at my wits end with this one. I have data that sits in hierarchy. I have total sales by store, but I have used the hierarchy feature to create a hierarchy which is Division -> Region -> Area -> Store. Let's say I want to rank total sales by Area. I have this DAX code which ranks by Area.
TotalSalesRankTest =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
DataADW,
EntityHierarchy[Area]),
"@AreaRanking" ,
RANKX( ALL(EntityHierarchy[Area]), [TotalSales], , DESC)),
[@AreaRanking] )
This works great except one big problem. I have a slicer for different regions and when I select a region, it reranks everything within that region only. For example in the picture below, 1, 2, 3, 4, and 9 are in the same region. If I select that region, 9 is now 5th and becomes 5. I would like it still be 9th even when filtered.
Any help anyone can provide would be appreciated.
Hi LXXVII ,
Hope everything is going well.
Please try:
RankMeasure = CALCULATE( RANKX( ALL('EntityTable'), CALCULATE(SUM('TotalSalesTable'[TotalSales])) ), ALLEXCEPT('EntityTable', 'EntityTable'[Store]) )Put the created measure value into the visual object, the page is as follows:
After applying the slicer, the page is as follows. You can see that the slicer does not affect the sorting.
Power BI Desktop files are attached.
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
8 Replies
Hi LXXVII ,
Hope everything is going well.
Please try:
RankMeasure = CALCULATE( RANKX( ALL('EntityTable'), CALCULATE(SUM('TotalSalesTable'[TotalSales])) ), ALLEXCEPT('EntityTable', 'EntityTable'[Store]) )Put the created measure value into the visual object, the page is as follows:
After applying the slicer, the page is as follows. You can see that the slicer does not affect the sorting.
Power BI Desktop files are attached.
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!Anonymous Bless you! This was exactly what I needed. Thank you very much.
- Daniel29195
Community Champion
TotalSalesRankTest = SUMX( ADDCOLUMNS( SUMMARIZE( DataADW, EntityHierarchy[Area]), "@AreaRanking" , RANKX( ALL(EntityHierarchy[Area],EntityHierarchy[Region]), [TotalSales], , DESC)), [@AreaRanking] )If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠Daniel29195
I just gave that a try and not only is it not making the ranking static, its now ranking incorrectly for some strange reason.- Daniel29195
- Ashish_Mathur
Super User
Hi,
Turn Off interaction between the Region slicer and the visual.
