DAX – Weighted Averages: Another Use of SUMX()

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.