Grand averaging a measure where there is missing data in PowerBI and DAX

Question

I am trying to get my head around DAX and am struggling. I have a PowerBI Matrix in which I need to calculate the average of a measure. The measure is '% of population' and on the surface it appears to work exactly as expected.

It calculates correctly in the top matrix for the two levels and also summarises correctly in the bottom table.

As an example, I have highlighted in red the order of calculations for "A3"

For the record the % population is set to

% of Population = sum(Data[Value])/sum('Level'[Population])

The problem occurs when I filter on the Country and only select Country 2...

Country 2 does not have data for "D13". Although the Values sum up correctly (170), the Sum of the Population includes the 300 from the missing D13 row making a total of 600 and the '% population' of 28.33% (instead of 170 / 300 = 57%)

I am happy to turn off the group totals in the top grid so that the 28.33 does not show; so my real problem is actually with the bottom grid.

I think I need a new measure to be displayed in the bottom grid. I think it simply needs to sum up the values and divide by the sum of the populations - but only when the value is present. How do I do that?

Or am I totally on the wrong track and there is an obvious answer that I am missing?

The PowerBI file can be downloaded from here

Thanks in advance.

Answer 1

The reason this is happening is that the Country table does not filter the Level table in the relationship diagram since they both only filter one way to the Data table and there are no other relationships.

Without changing your data model, one way to fix this in DAX is to specify that you only want to count Population where Level[LevelId] matches a Data[SecondLevelId] in your current filter context.

Population =
DIVIDE (
    SUM ( Data[Value] ),
    CALCULATE (
        SUM ( 'Level'[Population] ),
        'Level'[LevelId] IN VALUES ( Data[SecondLevelId] )
    )
)