Hi, Is there a way to build a power BI bar chart using multiple heirarchies and avoid calculating averages of averages when drilling up?

Question

I have a dataset with columns Country, State, City, Sales. I wanted to build a drill down bar chart to drill from country to State and then City, showing the average sales. My problem is that I can't find a workaround to avoid power bi calculating the average as the average of the inmediate lower heirarchy. Since I have States with much more cities than others, when I get to category Countries, the averages are wrong, because Two States with different amount of cities are weigthed the same way when summarizing to the upper level. Is there any way to define the granularity level on which averages should be calculated or any other workaround.

Example

example dataset

For country A, I want to show the average as 16. Currently is doing the average between States X and Y, whose averages are 17.2 and 13, giving 15.1 as a result.

Any help on how to solve this problem will be preciated. Thanks.

Answer 1

avgMeasure:= CALCULATE(AVERAGE(tbl[sales]),ALLEXCEPT(tbl,tbl[country]))