How to Calculate Weighted Average in Power BI

Calculating a simple average in Power BI is straightforward, but it can often hide the real story in your data. For a more accurate picture, you need a weighted average. This article will walk you through exactly how to calculate a weighted average in Power BI using DAX, giving your reports the nuance they need to be truly insightful.

What is a Weighted Average, Anyway?

Unlike a simple average where all values are treated equally, a weighted average gives more importance to certain values over others. Think about your final grade in a college class. Your final exam, worth 50% of your grade, has a much bigger impact (a higher "weight") on your overall score than a weekly quiz worth only 5%.

In business, this concept is everywhere:

• Product Reviews: A product with 10,000 five-star reviews should be considered more successful than a product with a single five-star review. The number of reviews acts as the weight.
• Sales Data: To find the average price of products sold, you can't just average the item prices. You need to weight each price by the number of units sold. A $10 item that sells 1,000 units has a far greater impact on revenue than a $500 item that sells only two units.
• Survey Results: If you survey customers across different regions, you need to weight the results by the number of respondents in each region to get a true company-wide average satisfaction score.

Ignoring these weights leads to misleading reports. Calculating a weighted average in Power BI ensures your analysis reflects the true business context.

The DAX Functions You'll Need

To build a weighted average measure, we'll rely on a few key DAX functions. Don't worry if you're not a DAX expert, the combination is quite simple once you see it in action.

1. SUMX()

This is the workhorse of our formula. SUMX() is what’s known as an "iterator" function. It works by going through a table, row by row, performing a calculation you define for each row, and then summing up the results of all those individual calculations. This is perfect for multiplying our value (like price) by our weight (like quantity) for every single row in our dataset.

2. SUM()

This one is simple. SUM() just adds up all the numbers in a single column. We'll use this to get the total of our weights (for example, the total number of units sold or total survey responses), which will be the denominator in our formula.

3. DIVIDE()

While you could just use the forward slash (/) for division in DAX, it's a best practice to use the DIVIDE() function instead. Why? Because it automatically handles division-by-zero errors. If you have a situation where your total weight is zero, DIVIDE() will return a blank or an alternate result you specify, preventing your visual from breaking with an ugly error message.

Step-by-Step Guide: Calculating Weighted Average Price

Let’s walk through the most common scenario: calculating the weighted average price of products based on the quantity sold. Imagine we have a simple table named 'Sales' with the following columns:

• Product
• Price
• Units Sold

A simple average of the 'Price' column would be misleading because it wouldn’t account for the fact that some products sell in much higher volumes. Here’s how to create a proper weighted average measure.

Step 1: Create a New Measure

First, navigate to the Modeling tab in the Power BI ribbon. Click on New Measure. This will open the formula bar where you can write your DAX expression.

Step 2: Define the Numerator (The Weighted Value)

The first part of a weighted average calculation is the sum of the value multiplied by its weight. We'll use SUMX() for this. The function needs two things: the table to iterate over ('Sales') and the expression to calculate for each row ('Price' * 'Units Sold').

The numerator part of our formula looks like this:

SUMX('Sales', 'Sales'[Price] * 'Sales'[Units Sold])

This formula tells Power BI to go through the 'Sales' table line by line, multiply the 'Price' by the 'Units Sold' for each line, and then add all those results together.

Step 3: Define the Denominator (The Total Weight)

Next, we need the sum of our weights. In this case, the weight is the number of units sold. We can get this with a simple SUM() function on the 'Units Sold' column.

The denominator part of our formula is:

SUM('Sales'[Units Sold])

Step 4: Combine Them with DIVIDE()

Now we just need to divide the numerator by the denominator using the DIVIDE() function. The final function will also include an "alternate result" of 0 in case the denominator is zero. This tells Power BI to return 0 instead of an error.

Here is the completed DAX formula for your "Weighted Average Price" measure:

Weighted Average Price = DIVIDE( SUMX('Sales', 'Sales'[Price] * 'Sales'[Units Sold]), SUM('Sales'[Units Sold]), 0 )

Hit Enter to save your measure. You can now drag this measure into a card, table, or chart to see your weighted average price. Because it’s a measure, it will dynamically recalculate based on any filters or slicers you apply to your report, giving you the weighted average price for a specific product category, time period, or region automatically.

Another Example: Weighted Average Customer Satisfaction Score (CSAT)

This pattern can be applied to many other scenarios. Let's say you have a 'Surveys' table containing CSAT survey results from different support agents. The table includes:

• Agent Name
• Average CSAT Score (for that agent)
• Number of Responses (for that agent)

An agent with a 100% CSAT score from 2 responses shouldn't be viewed the same as an agent with a 95% CSAT score from 200 responses. We need to weight the CSAT score by the number of responses.

Using the same pattern as before, we can construct the measure:

Give your new measure a name like "Overall Weighted CSAT" and use this DAX formula:

Overall Weighted CSAT = DIVIDE( SUMX('Surveys', 'Surveys'[Average CSAT Score] * 'Surveys'[Number of Responses]), SUM('Surveys'[Number of Responses]), BLANK() )

Here, the Overall Weighted CSAT score is correctly calculated by giving more weight to the agents who received more survey responses, providing a much fairer and more accurate view of overall team performance. Using BLANK() as the alternate result is also a good option, as it makes charts and tables appear empty instead of showing a zero if no data is available.

Tips for Success and Common Pitfalls

As you start implementing weighted averages, keep these few things in mind to avoid common issues.

Mind Your Filter Context

Remember that measures in Power BI operate within a "filter context." This is a huge benefit! It means your weighted average measure will automatically adjust based on its context in a report. If you put the Weighted Average Price measure in a matrix with product categories on the rows, it will calculate the weighted average for each category. No extra work needed!

Handling Relationships

If your data is split across tables (e.g., a 'Products' table with prices and a separate 'Sales' table with units sold), make sure your data model relationships are set up correctly. Your SUMX formula might need to use the RELATED() function to pull in a value from another table, like this:

SUMX('Sales', RELATED('Products'[Price]) * 'Sales'[Units Sold])

Explicit is Better than Implicit

When starting out, it’s best to be explicit. The DIVIDE(SUMX(...), SUM(...)) pattern is a clear, readable, and robust way to calculate weighted averages. It makes your DAX easy for colleagues (and your future self) to understand and troubleshoot. Stick to this pattern, and you'll find it solves nearly every weighted average scenario you encounter.

Final Thoughts

Mastering the weighted average calculation in Power BI is a big step toward creating more accurate and business-relevant reports. By swapping simple averages for weighted ones where appropriate, your dashboards will tell a truer story and empower your team to make smarter decisions based on data with the proper context.

Of course, writing a bit of DAX is just one piece of the reporting puzzle. We know that building dashboards across all your marketing and sales tools can feel like a full-time job. That's why we built Graphed. You can connect all your sources in just a few clicks, and instead of writing formulas, you just ask for what you want in simple terms, like "show me our weighted average CSAT score broken down by support agent this quarter." It creates the interactive dashboard instantly, letting you get straight to the insights.