cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AGo Member
Member

Calculate sum of a value of a flattened hierarchy

Hi all!

I'd like to have an histogram with as many drill down levels as the maximum depth of a hierarchy, and the sum of the price of each related level as value.

 

I've got a table with the price value and the ID, and a table with the flattened hierarchy. These 2 tables are now related using ID-NodeKey. This hierarchy has not a defined number of columns because it depends on how much levels of the hierarchy are resulting from another parent-child table. So if I update my data one day there could be one more column.

 

Is it possible to have a measure that works like "sum all the prices related to the values in the columns that contain the word "Level"" or a procedure with other calculated tables that works like so?

 

 

Cattura.JPG

 

 

The remaining problem is that if it works I'll have to update manually the levels in the histogram axis.

Thanks!

7 REPLIES 7
v-huizhn-msft Super Contributor
Super Contributor

Re: Calculate sum of a value of a flattened hierarchy

Hi @AGo,

Afther research, I a still confusing about your requirement. The number of level columns is not defined, how do you display it in hierarchy? Everytime, you add the level column to a hierarchy manully? 

And could you please list all your resource table and list the expected result, so I can analysis and post the solution which is close to your requirement.

Best Regards,
Angelia

AGo Member
Member

Re: Calculate sum of a value of a flattened hierarchy

For now the measure I use as value is this, but I have to manually add the levels as you said:

Measure = CALCULATE(SUM('IDprice'[Price]);FILTER('IDprice';PATHCONTAINS(RELATED(fnFlattenPCHierarchyFollowAlong[HierarchyPath]);'IDprice'[ID])))

 

The second table is like so:
Cattura.JPG

v-huizhn-msft Super Contributor
Super Contributor

Re: Calculate sum of a value of a flattened hierarchy

Hi @AGo,

You got the values using the measure, have you resolved your issue? 

Best Regards,
Angelia

AGo Member
Member

Re: Calculate sum of a value of a flattened hierarchy

No, because using this measure I have to manually add the levels.

Super User
Super User

Re: Calculate sum of a value of a flattened hierarchy

I you create a bridge-table in your model, you don't have to adjust your hierarchies manually: http://www.thebiccountant.com/2017/06/13/bill-of-material-bom-explosion-part2-costing-in-excel-and-p...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




AGo Member
Member

Re: Calculate sum of a value of a flattened hierarchy

Cant figure out how this method allows to automatically add levels to a not yet existing hierarchy to use it in a histogram with drill down. How did they add those 5 columns dynamically and automatically to that BOM_Hier hierarchy in that report?

Highlighted
Super User
Super User

Re: Calculate sum of a value of a flattened hierarchy

If you send me your tables, I can quickly set that up for you. I'm not the best explainer unfortunately, and you might understand that best on a working example.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 99 members 1,398 guests
Please welcome our newest community members: