Tabular Parent/Child Dimensions: SumOfLeaves and the CALCULATE wallhttp://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/20/tabular-parent-child-dimensions-sumofleaves-and-the-calculate-wall.aspxIn my last post about Parent/Child hierarchies , there is a question, in the comments, that I found interesting. Nevertheless, the formula is a complex one and cannot be written in a simple comment. Thus, I am making a follow-up to that post. I am notenCommunityServer 2.1 SP2 (Build: 61129.1)re: Tabular Parent/Child Dimensions: SumOfLeaves and the CALCULATE wallhttp://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/20/tabular-parent-child-dimensions-sumofleaves-and-the-calculate-wall.aspx#37143Wed, 20 Jul 2011 06:32:59 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:37143milang<p>I think this will be very useful for a lot of Excel people.</p>
<p>Thanks once again. :)</p>re: Tabular Parent/Child Dimensions: SumOfLeaves and the CALCULATE wallhttp://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/20/tabular-parent-child-dimensions-sumofleaves-and-the-calculate-wall.aspx#45436Tue, 02 Oct 2012 22:19:54 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:45436PaulC<p>Hi Alberto</p>
<p>It may seem an odd idea, but how would you write DAX to exclude all children from the aggregate? So Annabel = 600 and Bill = 0</p>
<p>I have a model I am populating from Hyperion extracts containing many calculated members. Aggregates are included in the extracts I have been provided. I want to present them in a Parent-Child hierarchy, but effectively disable aggregation up the hierarchy...</p>
<p>Thanks</p>
<p>Paul </p>re: Tabular Parent/Child Dimensions: SumOfLeaves and the CALCULATE wallhttp://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/20/tabular-parent-child-dimensions-sumofleaves-and-the-calculate-wall.aspx#45437Wed, 03 Oct 2012 10:19:50 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:45437AlbertoFerrari<p>Paul, it is pretty easy indeed, just need to use CALCULATE and a suitable filter that lists only the values for the current node.</p>
<p>Something like CALCULATE (SUM (Invoices[Amount]), Invoices[NodeId] = VALUES (Hierarchy[NodeId]) should work (I have not tried it, so syntax might be wrong).</p>
re: Tabular Parent/Child Dimensions: SumOfLeaves and the CALCULATE wallhttp://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/20/tabular-parent-child-dimensions-sumofleaves-and-the-calculate-wall.aspx#45440Wed, 03 Oct 2012 14:10:51 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:45440PaulC<p>Hi Alberto</p>
<p>Thanks very much for your advice.</p>
<p>CALCULATE (SUM (Invoices[Amount]), Invoices[NodeId] = VALUES (Hierarchy[NodeId])) gave me the "ERROR – CALCULATION ABORTED: Calculation error in measure ...: A table of multiple values was supplied where a single value was expected."</p>
<p>I tried wrapping the calculation with a HASONEVALUE check: IF(HASONEVALUE(Hierarchy[NodeId])),CALCULATE (SUM (Invoices[Amount]), Invoices[NodeId] = VALUES (Hierarchy[NodeId])),BLANK())</p>
<p>This gave partial results: It worked fine for Nodes with no children, but parent nodes were blank, with the correct value instead appearing in the "empty node" below the parent.</p>
<p>How can I modify the CALCULATE filter expression to only ever return the Parent row from Hierarchy?</p>re: Tabular Parent/Child Dimensions: SumOfLeaves and the CALCULATE wallhttp://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/20/tabular-parent-child-dimensions-sumofleaves-and-the-calculate-wall.aspx#45556Sat, 13 Oct 2012 20:44:51 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:45556AlbertoFerrari<p>Paul, looks like an interesting pattern, I need to play with the code, I will take a look and get back later with a solution (if I find one...)</p>
re: Tabular Parent/Child Dimensions: SumOfLeaves and the CALCULATE wallhttp://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/20/tabular-parent-child-dimensions-sumofleaves-and-the-calculate-wall.aspx#46258Fri, 16 Nov 2012 22:51:27 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:46258AlbertoFerrari<p>Paul, give this formula a try:</p>
<p>=IF (</p>
<p> [BrowseDepth] = [MinNodeDepth],</p>
<p> CALCULATE (</p>
<p> SUM (Invoices[Amount]),</p>
<p> FILTER (</p>
<p> Invoices,</p>
<p> Invoices[NodeId] = CALCULATE (</p>
<p> VALUES ('Hierarchy'[NodeId]),</p>
<p> FILTER (</p>
<p> 'Hierarchy',</p>
<p> 'Hierarchy'[NodeDepth] = MIN ('Hierarchy'[NodeDepth])</p>
<p> )</p>
<p> )</p>
<p> )</p>
<p> )</p>
<p>)</p>
<p>It seems to work. I guess I write a post about it, not very easy to read, but it does what you need</p>
re: Tabular Parent/Child Dimensions: SumOfLeaves and the CALCULATE wallhttp://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/20/tabular-parent-child-dimensions-sumofleaves-and-the-calculate-wall.aspx#46310Tue, 20 Nov 2012 16:37:10 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:46310PaulC<p>Many thanks Alberto</p>
<p>I had reverted to extra conditioning in my staging SQL database (keeping leaf values and calculating deltas for all other members), but that was a lot of effort and not what I really wanted.</p>
<p>I will adopt your DAX approach in my November release.</p>
<p>regards</p>
<p>Paul</p>re: Tabular Parent/Child Dimensions: SumOfLeaves and the CALCULATE wallhttp://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/20/tabular-parent-child-dimensions-sumofleaves-and-the-calculate-wall.aspx#50391Tue, 06 Aug 2013 14:24:55 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50391Dan<p>Hi, </p>
<p>Having played with the hierarchy in the worked example, I've found that Gerhard Brueckls method gave a good simplification of the above. The collection of measures I used was: </p>
<p>SumOfAmount:=IF (</p>
<p> [BrowseDepth] > [MinNodeDepth], </p>
<p> BLANK(),</p>
<p> SUM (Amounts[Amount])</p>
<p> )</p>
<p> - this fixes the display of (blank) in the hierarchy</p>
<p>GroupAmount:=IF([MaxNodeDepth]=[MinNodeDepth],BLANK(),[SumOfAmount])</p>
<p> - this restricts the calculation of the total for nodes which have direct children</p>
<p>TeamAmount:=CALCULATE (</p>
<p> SUM (Amounts[Amount]),</p>
<p>FILTER(VALUES('Employees'[NodeDepth]),</p>
<p>'Employees'[NodeDepth] > 'Employees'[BrowseDepth] ))</p>
<p> - this calculates only the contributions of the direct reports to the total at each node.</p>
<p>IndividualAmount:=CALCULATE (</p>
<p> SUM (Amounts[Amount]),</p>
<p>FILTER(VALUES('Employees'[NodeDepth]),</p>
<p>'Employees'[NodeDepth] = 'Employees'[BrowseDepth] ))</p>
<p> - This calculates the individuals contribution to the total at all levels.</p>
<p>and finally a dynamic values measure if you want to select from a slicer to show the values in one column. </p>
<p>Dynamic Values:=IF(COUNTROWS(VALUES('TotalsType'[Totals Type]))=1</p>
<p> ,IF(VALUES(TotalsType[Totals Type])="Team"</p>
<p> ,CALCULATE('Amounts'[TeamAmount])</p>
<p> ,IF(VALUES(TotalsType[Totals Type])="Group"</p>
<p> ,CALCULATE([GroupAmount])</p>
<p> ,IF(VALUES(TotalsType[Totals Type])="Individual"</p>
<p> ,CALCULATE('Amounts'[IndividualAmount])</p>
<p> ,IF(VALUES(TotalsType[Totals Type])="All"</p>
<p> ,CALCULATE('Amounts'[SumOfAmount])</p>
<p>)))))</p>
<p>NOTE: you will need a MaxNodeDepth measure and a table of TotalsType containing 'All', 'Group', 'Team', and 'Individual' added as a slicer (with a sort order if you like). </p>
<p>Hope this helps</p>re: Tabular Parent/Child Dimensions: SumOfLeaves and the CALCULATE wallhttp://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/20/tabular-parent-child-dimensions-sumofleaves-and-the-calculate-wall.aspx#50396Wed, 07 Aug 2013 06:12:02 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50396Dan<p>Alberto, </p>
<p>I think the previous submitted post contained errors - I realise that I am having difficulty calculating some measures which have context within a hierarchy. </p>
<p>One we are trying to complete is to calculate the totals for contributions from each member in the next lower level in the hierarchy. i.e. sum the individual contributions (as per Gerhard Brusckls post) and then add those up on their parent nodes. This would give us the teams contribution. I think there must be a simple answer but I can't find one. </p>
<p>Thanks</p>
<p>Dan</p>