0

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.

rafa_ape
  • 3
  • 2

1 Answers1

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

Solution

smpa01
  • 4,149
  • 2
  • 12
  • 23
  • Thanks, this really helped me move forward, I would like to add for anyone having issues in the future, the possibility of using the function ISFILTERED inside the messure calculation to adapt the formula to different levels of the drilldown. I used to not summarize the deepest level. – rafa_ape Jul 13 '21 at 09:26