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:

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

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

Read more on our blog

Get in touch with a P3 team member

  • Hidden
  • Hidden
  • This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

Related Content

Data Analytics Made Easy: Download Our Power BI DAX Reference Card

Let’s be honest, working with DAX can sometimes feel like a puzzle,

Read the Blog

Calculation Groups to the Rescue!

o set the stage, I need you to travel back in time

Read the Blog

A Brief Treatment of DIVIDE(), RANKX(), and “N/A”

Ya know, we could probably write articles about RANKX for an entire

Read the Blog

Power BI Brain Candy: Value Above Replacement

In today’s article, I’m aiming to achieve three overlapping goals:

Read the Blog