cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AGo Resolver I
Resolver I

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
Microsoft
Microsoft

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 Resolver I
Resolver I

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

Microsoft
Microsoft

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 Resolver I
Resolver I

Re: Calculate sum of a value of a flattened hierarchy

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

Super User III
Super User III

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...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

AGo Resolver I
Resolver I

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?

Super User III
Super User III

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors