sum up by hierarchy in a table and displaying it

[shakedb70]

Hi!

I have a table that looks like that:

where each name has a father (except for the ones at the first level) and only the names in the highest level has a total (the highest level doesn't habe to be 4).

I'm trying o sum up each name by it's sons total, so the final table should look like that:

in addition I want to show this table in the power bi in this format:

with an option to drill down and up.

Please if anyone can help, Im still new to power bi and learning on the way.

[mwegener · Accepted solution]

Hi @shakedb70 ,

in Power BI you can do this by using the PATH functions and displaying the hierarchy in a matrix.

First you determine all parent elements per level with PATH.

hierarchy path = PATH('Table'[Name],'Table'[father name])

Then you decompose the path with PATHITEM

hl1 = PATHITEM('Table'[hierarchy path],1)

For more info check out the attached sample file.

There is also a DAX Pattern post on the topic:

https://www.daxpatterns.com/parent-child-hierarchies/