Forum Discussion

LXXVII's avatar
LXXVII
Frequent Visitor
2 years ago
Solved

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 -> R...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 Team

     

    If 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!

Daniel29195's avatar
Daniel29195
Icon for Community Champion rankCommunity Champion
2 years ago

LXXVII 

 

could you please share the power file so i can take a look ? 

 

best regards

LXXVII's avatar
LXXVII
Frequent Visitor
2 years ago

Daniel29195 

 

I have a dummy file I constructed but I am not sure how to share it.

It has these tables:

Entity Table

Total Sales Table

 

Hierarchy is set up like this:

Measures:

 

TotalSales = CALCULATE(SUM(TestSalesTable[Total Sales]))
 
TotalSalesRank =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            TestSalesTable,
           TestHierarchy[Area]),
        "@AreaRanking" ,
       RANKX( ALL( TestHierarchy[Area]), [TotalSales], , DESC)),
    [@AreaRanking] )
 
Those tables and measures can be used to create the same problem on a smaller data set. If there is a way to post the file I would be happy to.
 
  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    LXXVII 

    is this what you want ? 

    so store 1 rank is 8 

     

    now i filter on region = 1 , 

    store1 ranking stays = 8

     

     

    ranking = 
RANKX(
    ALLSELECTED(table_h[store]),
    CALCULATE(SUM(table_h_sales[total sales]),REMOVEFILTERS(table_h[region]))
)

     

     

     

    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! 🤠