Reply
AGo Member
Member
Posts: 65
Registered: ‎03-08-2016

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!

Super Contributor
Posts: 3,609
Registered: ‎09-27-2016

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
Posts: 65
Registered: ‎03-08-2016

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

Super Contributor
Posts: 3,609
Registered: ‎09-27-2016

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
Posts: 65
Registered: ‎03-08-2016

Re: Calculate sum of a value of a flattened hierarchy

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

Super User
Posts: 1,626
Registered: ‎09-06-2015

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
Posts: 65
Registered: ‎03-08-2016

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
Posts: 1,626
Registered: ‎09-06-2015

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