Calculate the average age of population of each area

[Ankhi]

Hi ,

I have an excel source with columns like Area , Age Range and Population corresponding to each age and area . Something like below.

The age categories goes up till 100 years and there are multiple areas.

How do I find the average age of each area please? 

This is a sample extract from the excel.

Please help.

Thanks

Ankhi

Area	Age (101 categories)	Population
XyZ	Aged under 1 year	539
XyZ	Aged 1 year	583
XyZ	Aged 2 years	698
XyZ	Aged 3 years	674
XyZ	Aged 4 years	698
XyZ	Aged 5 years	765
XyZ	Aged 6 years	737
XyZ	Aged 7 years	752
XyZ	Aged 8 years	843
XyZ	Aged 9 years	812
XyZ	Aged 10 years	791
XyZ	Aged 11 years	790
XyZ	Aged 12 years	735
XyZ	Aged 13 years	767
XyZ	Aged 14 years	730
XyZ	Aged 15 years	680
XyZ	Aged 16 years	748
XyZ	Aged 17 years	667
XyZ	Aged 18 years	613
XyZ	Aged 19 years	488
XyZ	Aged 20 years	448
XyZ	Aged 21 years	546
XyZ	Aged 22 years	528
XyZ	Aged 23 years	554
XyZ	Aged 24 years	591
XyZ	Aged 25 years	605
XyZ	Aged 26 years	624
XyZ	Aged 27 years	600
XyZ	Aged 28 years	648
XyZ	Aged 29 years	675
XyZ	Aged 30 years	669
XyZ	Aged 31 years	708
XyZ	Aged 32 years	724
XyZ	Aged 33 years	807
XyZ	Aged 34 years	758
XyZ	Aged 35 years	796
XyZ	Aged 36 years	745
XyZ	Aged 37 years	767
XyZ	Aged 38 years	788
XyZ	Aged 39 years	843
XyZ	Aged 40 years	829
XyZ	Aged 41 years	851
XyZ	Aged 42 years	825
XyZ	Aged 43 years	831
XyZ	Aged 44 years	796
XyZ	Aged 45 years	874
XyZ	Aged 46 years	843
XyZ	Aged 47 years	828
XyZ	Aged 48 years	977
XyZ	Aged 49 years	974
XyZ	Aged 50 years	947
XyZ	Aged 51 years	926
XyZ	Aged 52 years	943
XyZ	Aged 53 years	910
XyZ	Aged 54 years	933
XyZ	Aged 55 years	931
XyZ	Aged 56 years	965
XyZ	Aged 57 years	875
XyZ	Aged 58 years	911
XyZ	Aged 59 years	845
XyZ	Aged 60 years	850
XyZ	Aged 61 years	777
XyZ	Aged 62 years	746
XyZ	Aged 63 years	847
XyZ	Aged 64 years	740
XyZ	Aged 65 years	714
XyZ	Aged 66 years	721
XyZ	Aged 67 years	678
XyZ	Aged 68 years	768
XyZ	Aged 69 years	713
XyZ	Aged 70 years	695
XyZ	Aged 71 years	756
XyZ	Aged 72 years	795
XyZ	Aged 73 years	881
XyZ	Aged 74 years	896
XyZ	Aged 75 years	661
XyZ	Aged 76 years	718
XyZ	Aged 77 years	623
XyZ	Aged 78 years	635
XyZ	Aged 79 years	484
XyZ	Aged 80 years	482
XyZ	Aged 81 years	514
XyZ	Aged 82 years	465
XyZ	Aged 83 years	433
XyZ	Aged 84 years	403
XyZ	Aged 85 years	330
XyZ	Aged 86 years	331
XyZ	Aged 87 years	256
XyZ	Aged 88 years	240
XyZ	Aged 89 years	213
XyZ	Aged 90 years	197
XyZ	Aged 91 years	150
XyZ	Aged 92 years	119
XyZ	Aged 93 years	110
XyZ	Aged 94 years	72
XyZ	Aged 95 years	52
XyZ	Aged 96 years	56
XyZ	Aged 97 years	23
XyZ	Aged 98 years	15
XyZ	Aged 99 years	17
XyZ	Aged 100 years and over	23
ABC	Aged under 1 year	1097
ABC	Aged 1 year	1177
ABC	Aged 2 years	1210
ABC	Aged 3 years	1225
ABC	Aged 4 years	1365
ABC	Aged 5 years	1299
ABC	Aged 6 years	1273
ABC	Aged 7 years	1342
ABC	Aged 8 years	1398
ABC	Aged 9 years	1403
ABC	Aged 10 years	1468
ABC	Aged 11 years	1435
ABC	Aged 12 years	1383
ABC	Aged 13 years	1382
ABC	Aged 14 years	1375
ABC	Aged 15 years	1315
ABC	Aged 16 years	1289
ABC	Aged 17 years	1241
ABC	Aged 18 years	1182
ABC	Aged 19 years	1014
ABC	Aged 20 years	998
ABC	Aged 21 years	1145
ABC	Aged 22 years	1232
ABC	Aged 23 years	1303
ABC	Aged 24 years	1372
ABC	Aged 25 years	1357
ABC	Aged 26 years	1441
ABC	Aged 27 years	1390
ABC	Aged 28 years	1493
ABC	Aged 29 years	1546
ABC	Aged 30 years	1606
ABC	Aged 31 years	1479
ABC	Aged 32 years	1463
ABC	Aged 33 years	1514
ABC	Aged 34 years	1455
ABC	Aged 35 years	1458
ABC	Aged 36 years	1433
ABC	Aged 37 years	1477
ABC	Aged 38 years	1449
ABC	Aged 39 years	1420
ABC	Aged 40 years	1449
ABC	Aged 41 years	1568
ABC	Aged 42 years	1404
ABC	Aged 43 years	1335
ABC	Aged 44 years	1419
ABC	Aged 45 years	1460
ABC	Aged 46 years	1578
ABC	Aged 47 years	1617
ABC	Aged 48 years	1836
ABC	Aged 49 years	1942
ABC	Aged 50 years	1918
ABC	Aged 51 years	1948
ABC	Aged 52 years	2014
ABC	Aged 53 years	2021
ABC	Aged 54 years	2027
ABC	Aged 55 years	1963
ABC	Aged 56 years	2075
ABC	Aged 57 years	1979
ABC	Aged 58 years	2014
ABC	Aged 59 years	1818
ABC	Aged 60 years	1779
ABC	Aged 61 years	1694
ABC	Aged 62 years	1722
ABC	Aged 63 years	1599
ABC	Aged 64 years	1646
ABC	Aged 65 years	1540
ABC	Aged 66 years	1449
ABC	Aged 67 years	1568
ABC	Aged 68 years	1471
ABC	Aged 69 years	1464
ABC	Aged 70 years	1517
ABC	Aged 71 years	1610
ABC	Aged 72 years	1626
ABC	Aged 73 years	1729
ABC	Aged 74 years	1679
ABC	Aged 75 years	1241
ABC	Aged 76 years	1334
ABC	Aged 77 years	1253
ABC	Aged 78 years	1121
ABC	Aged 79 years	931
ABC	Aged 80 years	792
ABC	Aged 81 years	785
ABC	Aged 82 years	808
ABC	Aged 83 years	672
ABC	Aged 84 years	607
ABC	Aged 85 years	535
ABC	Aged 86 years	457
ABC	Aged 87 years	422
ABC	Aged 88 years	396
ABC	Aged 89 years	308
ABC	Aged 90 years	253
ABC	Aged 91 years	222
ABC	Aged 92 years	177
ABC	Aged 93 years	143
ABC	Aged 94 years	113
ABC	Aged 95 years	72
ABC	Aged 96 years	73
ABC	Aged 97 years	39
ABC	Aged 98 years	24
ABC	Aged 99 years	16
ABC	Aged 100 years and over	29

[FarhanJeelani]

Hi @Ankhi ,

Try below steps:

Load your data: Load the data from Excel into Power BI.

Add a calculated column for the Age Midpoint: For the age categories, create a new column for the midpoint of each age category. You can use a SWITCH statement or a custom formula to handle the age ranges. Here's a formula for the midpoint:

AgeMidpoint =
SWITCH(
TRUE(),
'Table'[Age] = "Aged under 1 year", 0.5,
'Table'[Age] = "Aged 1 year", 1,
'Table'[Age] = "Aged 2 years", 2,
'Table'[Age] = "Aged 3 years", 3,
'Table'[Age] = "Aged 4 years", 4,
-- Continue for other age ranges --
'Table'[Age] = "Aged 100 years and over", 100
)

Add a weighted age column: Create a calculated column for the weighted age, which is the AgeMidpoint multiplied by the population.

WeightedAge = 'Table'[AgeMidpoint] * 'Table'[Population]

Create a measure to calculate the average age: You can now create a measure to calculate the average age of each area:

AverageAge =
DIVIDE(
SUM('Table'[WeightedAge]),
SUM('Table'[Population]),
0
)

Visualize the average age: Now, you can use a Matrix or Table visual to show the average age by area. Place Area in the rows and use the AverageAge measure in the values.

Please mark this post as solution if it helps you. Appreciate Kudos.