cancel
Showing results for
Did you mean:
Highlighted
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?

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

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

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:

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

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

## 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!

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

## 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!

Announcements

#### Announcing the New Spanish Forum

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

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘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