Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Issue with ranking with drill up/down

I'm currently trying to rank stores based off department performance against budget. I've tried to rank it off a measure for Variance percent, and by absolute variance dollars imported through an exc...
Need Help
Cmcmahan's avatar
Cmcmahan
Icon for Resident Rockstar rankResident Rockstar
7 years ago

Can you take a screenshot to show me how you have your Rows, Columns, and Values set up in your Matrix options?

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Matrix set up

  • Cmcmahan's avatar
    Cmcmahan
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Interesting. That should be working.  Are the values for Budget, Actual, and Rank2 behaving as expected?

     

    What are you using for the aggregation function for each field? When you click the down arrow on each value, it should have a section showing you which one you have.  If you're using a Count instead of a sum, that might be the issue.  You might even be able to calculate variance directly from here.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      The problem is the measure Rank2. I received error messages using rank.eq('Totals'[Var $],'Totals'[Var $]) due to not having a summation code, but adding a summation code spits out rank 1 for all the store managers. Using 

      Rank2 = rankx(ALL(Organization[Store]), sumx(RELATEDTABLE('Totals'),[Variance2])) spits out everyone as ones as well. Trying a couple of different iterations of them gave me gobidity **bleep** that didn't rank order them correctly, or once more spat out equal ranks of 1.
      • Cmcmahan's avatar
        Cmcmahan
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        Ahhh, I understand the issue now.  RANK.EQ is taking each area manager, summing up each of their subordinates, and then ranking them against every subordinate.  This leads to every area manager showing as being #1.

         

        Would something like this make sense?

         

        AreaRank = RANKX('Totals', SUMX(GROUPBY(Organization, RELATED(Organization[Area])), [Var $])

        This should rank the rows of the Totals table by the sum of [Var $], when grouped by the related [Area].

         

        And then a similar one for regional managers.  I'm sure there's some way to figure out the context you're in by using the hierarchy you set up instead of Organization[Area], but I'm not sure what it is without playing around more.