Forum Discussion

polestar_11's avatar
polestar_11
Frequent Visitor
2 years ago
Solved

Parent Child Hierarchy - Sum all Leafs on each NodeLevel

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.

 

DAX
Hierarchy
ParentChild
  • xifeng_L's avatar
    xifeng_L
    2 years ago

    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~

3 Replies

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist
    2 years ago

    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!

  • xifeng_L's avatar
    xifeng_L
    Super User
    2 years ago

    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's avatar
      polestar_11
      Frequent Visitor
      2 years ago

      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?