Summing / Aggregating with Parent Child Hierarchies

[BenCratsley]

Hi,

I am working with data that is structured in a parent-child relationship. Every level already has the rolled-up value (sum of the children). Therefore, I want Power Bi to display the value that is shown at every level (or sum on the same level) and not aggregate between parent and child. I do still need the parent-child relationship in PowerBi for drill through purposes. Attached is an example of how the data is structured. I have a dynamic number of levels, so ideally the solution wouldn't have a hard-coded number of levels. Any thoughts?

[v-janeyg-msft · Accepted solution]

Hi, @BenCratsley 

Create a measure to replace value.

Like this:

Measure = SUMX(FILTER('Table',[Item.2]<>BLANK()&&[Item.3]<>BLANK()),[Value])

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[ALLUREAN]

Hi, @BenCratsley 

Try using these 3 columns in your visual and expand the hierarchy button (Drill-Down)

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/

[BenCratsley]

Thanks for your reply! So its still showing the total sum (adding the parent and the child together). The picture above shows the basic structure of the data, but the picture below is actually what is getting fed into PowerBi.  

and this is a picture of the (wrong) graph using the dummy data above 

[ALLUREAN]

Can you share some sample data and screenshots of how this data is loaded in power bi?

Just a guess, you can extract Level3 by its components and create new three levels without the totals

[BenCratsley]

so the data come in like this and then I split by delimiter to brake it out into the levels. I don't think I can remove the totals, because sometimes the lower levels don't add up to the upper levels. (might be some missing data at the lower level, but its accurate at the rolled up level). I need a way to show the values as they are reported.

[ALLUREAN]

Try this example:

https://www.dropbox.com/s/ilhtour2cyfv3jm/Aggregating%20with%20Parent%20Child%20Hierarchies%28allure...

It is not the final solution, but you can try it