Calculate AVG count of children elements in herarchy

[Gazi]

Hi everyone!

I am having a hard time calculating average of children count in hierarchy.

For example: 

Parent Product A has 2 children: B and C.

B has 3 items and C has 5 items assigned (count). Total count is 8, but how to get the average count for A? which is 4.

Many thanks!

[v-yuezhe-msft · Accepted solution]

@Gazi,

Please check if the following measure returns your expected result. If not, please share sample data of your table.

average = CALCULATE(COUNTA(Table[Item]),ALLEXCEPT(Table,Table[Product],Table[Item]))/CALCULATE(DISTINCTCOUNT(Table[Children]),ALLEXCEPT(Table,Table[Product]))

Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@Gazi,

Please check if the following measure returns your expected result. If not, please share sample data of your table.

average = CALCULATE(COUNTA(Table[Item]),ALLEXCEPT(Table,Table[Product],Table[Item]))/CALCULATE(DISTINCTCOUNT(Table[Children]),ALLEXCEPT(Table,Table[Product]))

Regards,
Lydia

[Gazi · Accepted solution]

@v-yuezhe-msft

Hi Lydia!

Awesome!! It seems to be working! Sorry I forgot to mention - my hierarchy has 9 levels, not just 2 like in this example. So do you think I could extend your measure with a parameter or?

Many thanks!!!