Calculated Columns based on Hierarchy

[Anonymous]

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!

[Anonymous]

Hi @Anonymous ,

Have difficult about the logic.

Why the average of Organization ID 2 is (50+50)/2?

Where the second "50" from?

Best Regards,

Jay

[Anonymous]

Hi @Anonymous,

thanks for looking at my issue.

The Average formula is: (Own Sales Value + Sum (Average of child units)) / (Count of Child units + 1)

So in the case of Organization 2 its is (50 (Own Value) + 50 (Value Organization 4)) / 2

[Anonymous · Accepted solution]

Here is the example file: https://1drv.ms/u/s!Al1J7v_eCkDwaT2pgfu1neluZFY?e=VuEf9H