PowerPivot: Calculate ratio to parent

With SQL 2012 PowerPivot we have hierarchies at our disposal, one of the most common calculations we want to do with those hierarchies is doing a ratio to parent. This blog describes how you to do that.

I started with two simple tables, one a team / chapter table, the other sales by chapter:

Now I loaded these into PowerPivot, created a reletionship and a hierarchy:

Next I use the Hierarchy in a pivottable together with a Sum of SalesAmount measure:

Now we need a new measure that allows us to compare the actual sales per chapter with the sales of the team (or of all the chapters of the same team). This is a pretty  straightforward DAX formula once you understand my second phrase ” all the chapters of the same team”. What we in DAX can do is overwrite filtercontext and that is what we can do here.

If you take the same pivottable again but now we look at the highlighted cell:

What filters are used to calculate the sum of sales?  The filters here are “Hierarchy1 = Team” and “Hierarchy2=chapter1”. Now using calculate we can overwrite filters, so all we need to make sure of is that in our new measure we never listen to the Hierarchy2 filter, or in the DAX case we always filter Hierarchy2 by ALL values in the column. The reason that we can use ALL values in the Hierarchy2 column is that the other filter is still in place, Hierarchy1 is still filtered by “Team” so it will only return values for chapters that belong to the “Team”

This gives us the following DAX statement:

=CALCULATE([Sum of Sales],ALL(Table1[Hierarchy2]))

Gives us the following pivottable:

Now it is pretty simple to add a ratio formula:

=[Sum of Sales] / [Sum of sales all chapters]

That gives us the result we want:

So that turned out to be pretty easy once you understand the concept.

But what if you have multiple levels ? This needs a little more extended “Sum of Parent” formula since we need to override all level individually:

=if(HASONEVALUE(Table1[Hierarchy2]),
                    CALCULATE([Sum of Sales],all(Table1[Hierarchy2])) ,
                    CALCULATE([Sum of Sales],all(Table1[Hierarchy12]))
     )

Here we check if we are actually at the lowest level of the hierarchy “Table1[Hierarchy2]” if that is the case we override the filtercontext with that level. Otherwhise we override with the level above. This can be repeated if you have more levels: