Weighted average - help needed

[Anonymous]

Hello everyone,

I am learning PowerBI and am trying to calculate an average value.

I have the population size for a series of cities, across a number of years, and the population age

City	Year	Population age	Population size (for each age)
London	2008	1	100
London	2008	2	140
London	2008	3	98
London	2009	1	102
London	2009	2	130
London	2009	3	99
London	2010	2	118
Manchester	2008	1	58
Manchester	2008	3	60
Manchester	2009	2	61
Manchester	2009	3	55
Manchester	2009	4	70

I would like to calculate the average age for each city, keeping into account the population size (i.e. weighted average).

E.g. if in a city in a year there are: 100 5yr old people and 100 3yr old people, the average population age will be 4yr old. However, it there are 100 5yr and 200 3yr old people, the average population age will be lower.

I have tried to look at existing responses but I am totally lost 😞

Any help would be much appreciated, thank you in advance!

[Ashish_Mathur]

Hi,

Here is another way.  Create a calculated column formula

Numerator = Data[Population age]*Data[Population size]

Write this measure

Ratio = divide([numerator],SUM(Data[Population size]))

Hope this helps.

[AlexisOlson]

You'll need a SUM around the [numerator] in the Ratio.

[Ashish_Mathur]

Yes, that is correct.

[AlexisOlson · Accepted solution]

Weighted average can be computed like this:

AvgAge =
DIVIDE (
    SUMX ( Table1, Table1[Age] * Table1[Size] ),
    SUMX ( Table1, Table1[Size] )
)

[Anonymous]

Thank you @AlexisOlson !

Would it be the same if I wrote:

AveAge = DIVIDE(SUMX (Table1, Table1[Age] * Table1[Size]), SUM(Table1[Size])) ?

I don't understand why you are using the SUMX within the DIVIDE function....

Thank you!

AvgAge =
DIVIDE (
    SUMX ( Table1, Table1[Age] * Table1[Size] ),
    SUMX ( Table1, Table1[Size] )
)

[AlexisOlson]

Yeah, it should be the same.

I kept the SUMX in the denominator only for aesthetic reasons to emphasize the parallel with the numerator.