Mastering Weighted Averages with SUMX

Introduction

In our last lesson, we mastered the use of SUMX and AVERAGEX to perform calculations that weren't possible with simple aggregations, like calculating total revenue without a pre-existing revenue column. You learned that these iterator functions work by creating a temporary row context, allowing them to evaluate an expression for each row before aggregating the results.

Today, we'll build directly on that knowledge to tackle a very common and important business calculation: the weighted average. A simple average can often be misleading. For instance, in your logistics background, the simple average cost of a shipment doesn't tell the full story if some shipments are tiny and others are massive. We need to give more "weight" to the larger shipments.

This lesson will teach you how to use SUMX to calculate these more accurate, weighted averages. You'll learn the standard DAX pattern for this calculation and understand the crucial difference between a simple (unweighted) and a weighted average, ensuring you choose the right tool for your analysis.

What is a Weighted Average?

Before we write any DAX, it's essential to understand the concept. A simple average treats every value equally. A weighted average, however, assigns more importance to some values based on a "weighting" factor.

For a clear explanation and some business examples, please read the first part of the following article.

As the article explains, to find the true average price of products sold, you can't just average the prices. You must weight each price by the number of units sold.

The mathematical formula for a weighted average is:

Let's see how we can translate this into a DAX measure.

The DAX Pattern for Weighted Averages

Translating the formula above into DAX is surprisingly straightforward, and it relies on functions you already know, with SUMX doing the heavy lifting.

To implement this, we'll follow a clear, reusable pattern. The article you just started reading provides an excellent step-by-step guide.

So, the standard DAX pattern for a weighted average price is:

Weighted Average Price =
DIVIDE (
    SUMX ( 'Sales', 'Sales'[UnitPrice] * 'Sales'[OrderQuantity] ),
    SUM ( 'Sales'[OrderQuantity] )
)

Let's break this down:

• SUMX('Sales', 'Sales'[UnitPrice] * 'Sales'[OrderQuantity]): This is our numerator. Just like in our last lesson, SUMX iterates through the Sales table. For each row, it multiplies the UnitPrice (our value) by the OrderQuantity (our weight). It then sums up all these results. This is exactly the SUM (Value * Weight) part of our formula.
• SUM('Sales'[OrderQuantity]): This is our denominator. It's a simple aggregation that calculates the total of our weights—in this case, the total number of units sold.
• DIVIDE(...): We wrap the entire calculation in the DIVIDE function to prevent errors if the total quantity sold is zero.

The image below shows this exact pattern being used to calculate Weighted Sales based on Duration.

Weighted vs. Unweighted Averages: A Critical Distinction

You might now be wondering, "When would I use AVERAGEX versus this new pattern?" This is a fantastic question, and the answer gets to the heart of choosing the correct analysis for the business problem.

• AVERAGEX calculates an unweighted average of a row-level expression. It treats every row as equally important.
• The DIVIDE(SUMX, SUM) pattern calculates a weighted average. It gives more importance to rows with a higher weight.

Let's consider an example: calculating the average profit margin.

1. Unweighted Average Margin: AVERAGEX('Sales', [Profit] / [Sales]). This calculates the margin for each individual sale and then finds the simple average of all those margin percentages. A $10 sale with a 50% margin is treated the same as a $1,000,000 sale with a 10% margin.
2. Weighted Average Margin: DIVIDE(SUM('Sales'[Profit]), SUM('Sales'[Sales])). This calculates the total profit and divides it by the total sales. This is the true overall profit margin, where larger sales have a naturally larger impact.

The following video provides an excellent explanation of this difference, complete with a simple Excel demonstration that makes the concept very clear.

Understanding this distinction is key. As an analyst, you need to decide which question you are answering: "What is the average of my transaction-level margins?" (unweighted) or "What is my overall business's profit margin?" (weighted).

Practical Application

Now it's your turn to apply this pattern. We will create a Weighted Average Price measure in our report.

1. Open your AdventureWorks Sales Analysis.pbix file.
2. Create a new measure in your Measures table (or the Sales table if you don't have a dedicated one).
3. Name the measure Weighted Average Price.
4. Write the DAX formula using the DIVIDE(SUMX, SUM) pattern we discussed.

   Weighted Average Price =
   DIVIDE (
       SUMX (
           'Sales',
           'Sales'[UnitPrice] * 'Sales'[OrderQuantity]
       ),
       SUM ( 'Sales'[OrderQuantity] )
   )
   

5. Format the new measure as a currency with two decimal places.
6. To test it, create a table visual on your report page.
7. Add Product[Category] to the table.
8. Add your new Weighted Average Price measure.
9. For comparison, create a simple Average Price measure: Average Price = AVERAGE('Sales'[UnitPrice]). Add this to your table as well.
10. Compare the results. You will notice the values are different. The Weighted Average Price gives a truer representation of the average price point for sales in that category, while the simple Average Price just averages all the product unit prices regardless of how well they sell.

Conclusion

Great work! You've added another powerful and practical tool to your DAX toolkit. Moving beyond simple averages to weighted averages is a significant step in producing more accurate and insightful business analysis.

Key Takeaways:

• A weighted average gives more importance to some values based on a weighting factor, providing a more accurate picture than a simple average.
• The standard DAX pattern for weighted average is DIVIDE( SUMX(<table>, <value> * <weight>), SUM(<table>[weight]) ).
• SUMX is the core of this pattern, used to calculate the weighted value for each row (the numerator).
• It's crucial to distinguish between an unweighted average (often calculated with AVERAGEX) and a weighted average to answer the correct business question.

Preview of the Next Lesson:
So far, our iterator functions (SUMX, AVERAGEX) have always operated on an entire table (within the current filter context). But what if you need to perform a calculation on a dynamically filtered subset of that table? In our next lesson, we will introduce the powerful FILTER function, which allows you to create complex, table-based filtering conditions directly inside your measures.