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 

 

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

 

LXXVII's avatar
LXXVII
Frequent Visitor
2 years ago

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