Forum Discussion
Weighted average - help needed
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!
Weighted average can be computed like this:
AvgAge = DIVIDE ( SUMX ( Table1, Table1[Age] * Table1[Size] ), SUMX ( Table1, Table1[Size] ) )
6 Replies
Weighted average can be computed like this:
AvgAge = DIVIDE ( SUMX ( Table1, Table1[Age] * Table1[Size] ), SUMX ( Table1, Table1[Size] ) )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] ) )Yeah, it should be the same.
I kept the SUMX in the denominator only for aesthetic reasons to emphasize the parallel with the numerator.
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.
You'll need a SUM around the [numerator] in the Ratio.
Yes, that is correct.
