Another Question from the Mr. Excel Forums
Got a question on the forums the other day. It took some extra twists and turns but the simplest version of the question is worth covering here: how do I perform a weighted average?
Let’s say you have some data about ZIP (postal) codes in the United States:
And you build a simple pivot that shows total population and median age for each ZIP, grouped by that last column, which is how fast the population of that ZIP is growing:
Simple SUM Measure for Population, Simple AVERAGE for Median Age
But that “Average Median Age” measure is just:
[AVERAGE MEDIAN AGE]=
which treats all ZIP codes as equal, even if they have wildly different populations:
The Median Age of Very Low-Population ZIP Codes (Like Pop=277)
is Counting Equally to Higher-Pop ZIP Codes (Like Pop=21484)
Correcting so that Median Age is Weighted by Population
Let’s write a new measure:
[Average Med Age Weighted by Pop]=
SUMX(Zips, Zips[TTL Pop] * Zips[Med Age]) / [Total Population]
And then view the results:
PowerPivot Weighted Average Measure Compared to Non-Weighted Average
And that is what we’d expect – since the smaller ZIP codes of population 277 and 427 both have very young median ages, they were “unfairly” skewing the subtotal younger. When overall, fast-growing ZIPs have a lower median age once you weight them for population.
Even cooler – I can remove the ZIP code field from the pivot and the weighted average measure still works:
Weighted Average Measure Works Even When
the “Child” Field (ZIPCode) is not on the Pivot
For more information on SUMX(), see this post.