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:

image

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:

image

Simple SUM Measure for Population, Simple AVERAGE for Median Age

But that “Average Median Age” measure is just:

[AVERAGE MEDIAN AGE]=
   AVERAGE(Zips[Med 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)

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

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

Weighted Average Measure Works Even When
the “Child” Field (ZIPCode) is not on the Pivot

For more information on SUMX(), see this post.