Parent Child Hierarchy - Sum all Leafs on each NodeLevel

[polestar_11]

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.

[xifeng_L · Accepted solution]

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~

[polestar_11]

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?

[ahadkarimi]

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!