Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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 works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

1 REPLY 1

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 works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.