A Power BI Technique Mined from the Power Pivot Archives
Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI. Hard to imagine - even for us - but true.
Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.”
Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables. Enjoy!
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.