Issue with ranking with drill up/down

[Anonymous]

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 excel file, to no success. It doesn't scale up to area or region values. The relationships between the worksheets work appropriately, and I have a slicer to select the department. Thank you for any assistance in advance.

Totals TableOrganization Table

[Cmcmahan]

You should be able to accomplish this by setting up a hierarchy.  Right click the highest level, and select New hierarchy. Right click the next level and select Add to hierarchy, and so on.

For your visuals, use the region/area/store values from the hierarchy, and it should recognize that it needs to roll the sums up.

[Anonymous]

I have a hierarchy established already, unfortunately the ranking appears as flat lined on the area level with all the areas having the same value, which is one more than the number of stores.

[Cmcmahan]

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

[Anonymous]

Matrix set up

[Cmcmahan]

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]

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]

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.