Calculate sum of a value of a flattened hierarchy

[AGo]

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!

[v-huizhn-msft]

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]

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:

[v-huizhn-msft]

Hi @AGo,

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

Best Regards,
Angelia

[AGo]

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

[ImkeF]

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

[AGo]

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?

[ImkeF]

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.