Measure for using parent(s) costs in lower levels of parent-child hierarchy

[jkeizer]

Hi!

I have a parent-child hierarchy and a cost table. Costs can relate to any level of the hierarchy. Using this tutorial I've set up the hierarchy levels in Power BI.

What I want is to do calculations based on the parent(s) costs when looking at lower levels of the hierarchy.

For example:

Level 1 Spain = 50
Level 2 Madrid = 10
Level 2 Barcelona = 15

Should be visible as:

Level 1 Spain - 75

Level 2 Madrid - 35 (10 + 50/2)
Level 2 Spain-East - 40 (15 + 50/2)

It should also work when drilling down without showing the parent, no blank rows or leftover costs. E.g.:

Level 2 Madrid - 35 EUR
Level 2 Barcelona - 40 EUR

I'm having difficulties achieving this, especially when there are multiple levels.

How do I set up a measure to get this working?

[Anonymous]

Hi @jkeizer,

How many hierarchy levels your table has? Did you mean these calculations need dynamic iterator with previous level calculations results?

If this is a case, you need to manually add variables to calculate out the different level results, add if statement to check current row contents level and return specific level results. (Dax formula does not support recursion calculations and dynamic loop multiple level calculations, you need to manually calculate these results with filters)
Regards,

Xiaoxin Sheng

[amitchandak]

@jkeizer , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

I think isinscope should help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[jkeizer]

I have created this with some random data:

https://gofile.io/d/L2SX8o

Thanks in advance!