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

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

Thanks!

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

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:

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

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

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

Proud to be a Datanaut!

Imke Feldmann

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

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

Proud to be a Datanaut!

Imke Feldmann

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 99 members 1,398 guests
Recent signins: