cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shakedb70
Frequent Visitor

sum up by hierarchy in a table and displaying it

Hi!

I have a table that looks like that:

shakedb70_1-1660041908442.png

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:

shakedb70_2-1660042043839.png

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

shakedb70_4-1660042300023.png

with an option to drill down and up.

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

1 ACCEPTED SOLUTION
mwegener
Super User
Super User

Hi @shakedb70 ,

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

SumUpHierarchy.png

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)

SumUpHierarchy_dax.png

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/

 

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

1 REPLY 1
mwegener
Super User
Super User

Hi @shakedb70 ,

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

SumUpHierarchy.png

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)

SumUpHierarchy_dax.png

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/

 

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.