### 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]=

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)**

### 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**.