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:

 

  • Gottfried Eder

    Hi,

    I was – and still am – looking for an example of how to accomplish exactly what the article describes. Unfortunately, the solution is flawed as HASONEVALUE is not a reliable way to determine the current level. If a parent has only one child, in our example, the own sum of sales amount will be returned instead of the parent’s. This can easily be reproduced with the “Music, Movies and Audio Books” category in the Contoso database.

  • Yes, Even I am facing the same issue. When a parent has only one child, its not returning parents sales amount. I am using adventure works, Product cat->subcat->product hierarchy