How to calculate weighted average by group in DAX: step by step

João Barros 03 de August de 2026 5 min read

Let's calculate a weighted average by group in DAX — for example, the price average weighted by quantity per category. This is useful when you want a representative value that takes into account the weight of each row (quantity, importance, score). In business reports, for instance, a simple average can be misleading: if you sold 2 units at €100 and 100 units at €50, the simple average of the prices is (100+50)/2 = €75, while the quantity-weighted average is (100*2 + 50*100) / (2+100) = €51.92, which better reflects the effective average price.

Prerequisites

Power BI Desktop (or Excel with data model and DAX).
A fact table with columns: Category, Price, Quantity. Ideally, the table is named 'Sales' and the columns 'Sales'[Price], 'Sales'[Quantity], 'Sales'[Category].
Basic knowledge of measures and filter context in DAX: understanding row context vs filter context, and how CALCULATE changes filters.
Sample data with anywhere from dozens to thousands of rows — the approach works in both cases; just be mindful of performance if you have millions of rows.

Step 1: Understand what a weighted average is

A weighted average computes the SUM(Value * Weight) divided by SUM(Weight). Practically, for each category we want: SUMX(lines, Price * Quantity) / SUM(Quantity). To see with concrete numbers, suppose Category = "A" has three records:

Row 1: Price = 10, Quantity = 2
Row 2: Price = 12, Quantity = 3
Row 3: Price = 9, Quantity = 5

The numerator is 10*2 + 12*3 + 9*5 = 20 + 36 + 45 = 101. The denominator is 2+3+5 = 10. The weighted average is 101 / 10 = 10.1. Note that using SUM('Sales'[Price]) / COUNTROWS('Sales') would give a different simple average; this is why we need to multiply by Quantity on each row.

Step 2: Create the base measures

First create two simple measures: the numerator (weighted sum) and the denominator (sum of the weights). These measures are reusable and avoid repetition. Using SUMX ensures the calculation is done per row of the fact table before aggregating — this is crucial when you have multiple columns or expressions per row.

WeightedAmount = SUMX( 'Sales', 'Sales'[Price] * 'Sales'[Quantity] )
TotalQuantity = SUM( 'Sales'[Quantity] )

Practical notes: if the Quantity column can have nulls, ensure it is zero or use COALESCE('Sales'[Quantity],0). Format TotalQuantity as integer and WeightedAmount as decimal with 2-4 decimal places as needed.

Step 3: Create the safe weighted average measure

Now create the final measure that divides the numerator by the denominator. Use DIVIDE to avoid division by zero and ensure clean results when there is no data in the context. Also, it is good practice to use VAR to calculate each component once, which helps performance and readability.

WeightedAverage Price =
DIVIDE(
    [WeightedAmount],
    [TotalQuantity]
)

Another option, with explicit VARs, is:

WeightedAverage Price =
VAR Num = [WeightedAmount]
VAR Den = [TotalQuantity]
RETURN DIVIDE(Num, Den)

Formatting the measure to two decimal places is common. If you have percentages or scores with a different scale, adjust the formatting accordingly.

Step 4: Handle scenarios with filters or different granularities

If you want to calculate the weighted average always by Category, regardless of other filters, use ALL or ALLEXCEPT. For example, if you have a slicer by Product and you want the average by Category ignoring the product slicer, ALLEXCEPT keeps the Category filter and removes the others.

WeightedAverage Price by Category =
VAR Num =
    CALCULATE(
        SUMX('Sales', 'Sales'[Price] * 'Sales'[Quantity]),
        ALLEXCEPT('Sales', 'Sales'[Category])
    )
VAR Den =
    CALCULATE(
        SUM('Sales'[Quantity]),
        ALLEXCEPT('Sales', 'Sales'[Category])
    )
RETURN
DIVIDE(Num, Den)

Example: if Category = "A" has 100 units, but a Product slicer limits it to 20 units, the normal measure will show the value for the 20 units; the ALLEXCEPT version will show the value for the 100 units of the Category. Use this approach carefully — you may contradict users' expectations if you override filters applied in the report.

Step 5: Example with complex filters (dates and products)

When you have separate dimensions (Date, Product, Category), ensure you use the fact table for weights and values and use appropriate relationships. If you want the weighted average only for the current year, combine with FILTER. A robust way is to filter the date table explicitly:

WeightedAvg This Year =
VAR Num =
    CALCULATE(
        SUMX('Sales', 'Sales'[Price] * 'Sales'[Quantity]),
        FILTER(ALL('Date'), YEAR('Date'[Date]) = YEAR(TODAY()))
    )
VAR Den =
    CALCULATE(
        SUM('Sales'[Quantity]),
        FILTER(ALL('Date'), YEAR('Date'[Date]) = YEAR(TODAY()))
    )
RETURN
DIVIDE(Num, Den)

This way you can combine time filters with other slicers. If relationships between tables are not active, you may need USERELATIONSHIP; if you have multiple years and want ALLSELECTED to respect user selections, use ALLSELECTED('Date').

Verify the result

Place Category in a table visual and add the measures WeightedAverage Price and TotalQuantity. Manually verify one row: compute in a spreadsheet the sum(Price*Quantity) and sum(Quantity) for the category and confirm the division matches. Common mistakes: forgetting SUMX (leading to multiplying aggregates like SUM(Price)*SUM(Quantity) which is wrong), not using DIVIDE (can cause division by zero errors) or applying ALLEXCEPT incorrectly and losing important filters. Also test scenarios with 0 quantities and null values.

Conclusion

With these measures you can calculate robust weighted averages in different filter contexts. Next steps: apply the technique to other variables (costs, scores) and compare with simple averages. Practical tip: always validate at least 3 rows manually (small example set) and then test with larger aggregates; this helps understand where filter context influences the result. If you need, share a concrete example and I will help debug.