Forum Discussion
Calculated Columns based on Hierarchy
Hello together,
I would like to perform the following calculation in PowerBI using calculated columns:
We have an organizational structure that can have any number of levels. For each organization no matter what level we have given the following columns:
| Organization ID | Parent ID | Sales |
| 1 | 20 | |
| 2 | 1 | 50 |
| 3 | 1 | 20 |
| 4 | 2 | 20 |
| 5 | 4 | 50 |
| 6 | 4 | 70 |
Now I would like to calculate for each organization the average of the "sales" of its subsidiary units. Including its own value. I would have solved this normally via a table for each level but the number of levels is constantly changing and I don't want the dashboard to have to be adjusted each time. See the following chart as an example of the levels in the table above.
So instead of creating many tables I would like to put the calculation in the following table where all the organizations are included.
| Organization ID | Parent ID | Sales | Sales Average |
| 1 | 20 | (20+50+20)/3 | |
| 2 | 1 | 50 | (50+50)/2 |
| 3 | 1 | 20 | 20/1 |
| 4 | 2 | 20 | (20+70+60)/3 |
| 5 | 4 | 60 | 60/1 |
| 6 | 4 | 70 | 70/1 |
So far I have only one column in which I calculate the sum of all directly linked units but without their daughter units and without dividing by the number for the average.
How would you approach this, has anyone had a similar problem?
Thanks a lot for your answers!
Here is the example file: https://1drv.ms/u/s!Al1J7v_eCkDwaT2pgfu1neluZFY?e=VuEf9H
3 Replies
Here is the example file: https://1drv.ms/u/s!Al1J7v_eCkDwaT2pgfu1neluZFY?e=VuEf9H