PowerPivot, Parent/Child and Unary Operators

Following my last post about parent/child hierarchies in PowerPivot, I worked a bit more to implement a very useful feature of Parent/Child hierarchies in SSAS which is obviously missing in PowerPivot, i.e. unary operators. A unary operator is simply the aggregation function that needs to be used to aggregate values of children over their parent.

Unary operators are very useful in accountings where you might have incomes and expenses in the same hierarchy and, at the total level, you want to subtract expenses from incomes, in order to get a meaningful total. Clearly, you might obtain a good result adding a sign in front of the values in the fact table but, doing so, you miss the opportunity to create more than one hierarchy over the same data where values might get different signs in different hierarchies. For example, I might have a hierarchy where expenses are subtracted from the total and another one, with a different structure, where the expenses need to be summed with other values. Thus, I suppose that signing the value in the fact table is not a viable solution and I want to put the signs into the hierarchy.