cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable
3 REPLIES 3
Community Support

Hi @Anonymous ,

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

Where the second "50" from?

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

Hi @v-jayw-msft,

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
Not applicable

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.