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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
twilbour
Helper I
Helper I

Parent/Child Hierarchy - Show parent amount as its own row when filtered to next level

I'm working on a sales report that has 3 potential levels of a hierarchy.  Sales can occur at all levels.  Levels are Area Manager, Manager, Associate.  I've built all the level functions into my table (flattened).  I've built the paths and depths.  I've read everything I can find, but I can't see to figure out how to show a sales amount for a "parent", without showing blanks for every level, and preferably I'd like to see the parent label repeated, instead of "blank".  This is in Power Pivot - but it is the same problem in BI.  The link is Example Workbook  a sample of my problem.   

 

Annabel (an Area Manager) has 3 managers under her (and no associates under them).

Bill (Area Manager) has 2 managers (Brad and Julie).  Brad has 2 associates.  Julie has none.

 

Annabel has made some sales, and Brad has made some sales.  Bill hasn't.

 

Here is side by side of what I get on left that allows me to show Annabels individual contribution - forces every level to drill all the way to level 3, even though there's no child.  I'd also like the parent level label to repeat. On the right is how I would like the table to look as I drill from 1 to 3.

 

twilbour_1-1633024492838.png

 

 

 

I've done several formulas where you can hide blanks if row depth is less than browse depth, but then I don't see Annabel's sales at all.  The sum of the children doesn't equal the total for Annabel.  Her amount is never displayed.

 

This has been making me crazy!  All ideas welcome.

 

Thank you.

 

 

 

3 REPLIES 3
lbendlin
Super User
Super User

Please refer to this article. It explains the mechanics in excruciating detail.

Parent-child hierarchies – DAX Patterns

Thank you for your reply.  All of this is already incoporated into my code.  I have followed this post in great (as you said excruciating detail).  This code does not accomplish the final detail of being able to show a parent's contribution to a total when the children are displayed.  That's the kicker.

Anonymous
Not applicable

I'm facing the exact same problem and I've referenced the article linked. Were you able to find a solution?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors