Recursive DAX for Hierarchy

[Gazi]

Hi everyone!

I have the following DAX statement to calculate AVG count for one level below (Thanks to Lydia! @v-yuezhe-msft): 

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

But problem is I have 9 levels in hierarchy. Any ideas how to extend this DAX statement??

Many many thanks!!

[Greg_Deckler · Accepted solution]

Well, I can tell you that you can't do recursion in DAX, DAX hates it. 

Can you refer back to the original problem perhaps? Need the data in a copy and paste format to recreate and need to better understand what you want to do. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490