Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
94 | |
82 | |
70 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |