topic Re: Parent Child Hierarchy - Sum all Leafs on each NodeLevel in DAX Commands and Tips

Parent Child Hierarchy - Sum all Leafs on each NodeLevel

polestar_11 — Wed, 28 Aug 2024 09:23:05 GMT

Hello everyone

I hope you can help me with my Parent Child Hierarchy problem.

I want to use a Dax measure to make the amount from the fact table (on hierarchy leaves) available on all node levels.
The specialty is that I want to use the flat hierarchy column “Name” from the table “Hierarchy” in the visual.

Without the following solutions:
Using a hierarchy
Lev1-3 or LevName1-3 in scope of the visual
Calculation as a column and not as a separate dax measure

In the attached picture you can see the test data and the desired result.

Re: Parent Child Hierarchy - Sum all Leafs on each NodeLevel

ahadkarimi — Wed, 28 Aug 2024 15:25:40 GMT

Hi polestar_11, try these measures below, and if you encounter any issues, let me know.

Create a measure for Total Amount:

Total Amount = SUM('Fact Table'[Amount])

Create a measure for Hierarchy Aggregation

Aggregated Amount = VAR CurrentNode = MAX('Hierarchy'[Name]) RETURN CALCULATE( [Total Amount], FILTER( ALL('Hierarchy'), PATHCONTAINS('Hierarchy'[Path], CurrentNode) ) )

Did I answer your question? If so, please mark my post as the solution!✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!

Re: Parent Child Hierarchy - Sum all Leafs on each NodeLevel

xifeng_L — Wed, 28 Aug 2024 15:52:28 GMT

Hi polestar_11

You can try below measure.

Measure = SUMX( SUMMARIZE('Hierarchy','Hierarchy'[Name],'Hierarchy'[ID]), CALCULATE( SUM('Fact'[Amount]), TREATAS( CALCULATETABLE( VALUES('Hierarchy'[ID]), PATHCONTAINS('Hierarchy'[Path],EARLIER('Hierarchy'[ID])), ALL() ), 'Fact'[ID] ) ) )

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Parent Child Hierarchy - Sum all Leafs on each NodeLevel

polestar_11 — Wed, 28 Aug 2024 19:18:26 GMT

That works perfect. What is the Reason that it doesn't work with a relation between the two tables? Is there a solution with a relation?