Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Weighted average can be computed like this:
AvgAge =
DIVIDE (
SUMX ( Table1, Table1[Age] * Table1[Size] ),
SUMX ( Table1, Table1[Size] )
)
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |