How to calculate weighted standard deviation in DAX: step by step

Calculating weighted standard deviation in DAX lets you assess the dispersion of a variable when each observation has a different weight — for example, prices with different sales volumes or ratings with different numbers of responses. Using weights makes the measure more representative: an observation with large volume should influence the mean and dispersion more than an observation with an insignificant weight. This technique avoids biases caused by treating all observations as equally relevant.

Prerequisites

• Power BI Desktop or another tool that supports DAX.
• Fact table with columns: Value (value to analyze) and Weight (weight, e.g.: quantity or sales). Minimum example: 6–10 sample rows to test.
• Date table or optional time context to apply filters by month/year.

Practical recommendations: ensure weights are non-negative (negative weights require specific statistical interpretation) and that there are not many blank values. If the table has millions of rows, test on a sample before applying the logic to the entire dataset.

Step 1: Understand the weighted standard deviation formula

Before writing DAX, understand the mathematical formula. For observations xi with weights wi, the weighted mean is:

weighted_mean = (Σ wi * xi) / (Σ wi)

The weighted variance (population) is usually defined as:

weighted_variance = (Σ wi * (xi - weighted_mean)^2) / (Σ wi)

If you need a sample correction (equivalent to n-1), adjust the denominator — for example, (Σ wi - 1) — but this can be controversial when weights are not integer counts. In business analysis many reports use the population version because weights represent observed totals (e.g.: units sold) and not random samples.

Step 2: Create the weighted mean measure

Create the weighted mean first because it is needed for the variance. Assuming table Sales with Value and Weight, the measure is:

Weighted Mean =
VAR SumWeights = SUM(Sales[Weight])
VAR SumWeightedValues = SUMX(Sales, Sales[Value] * Sales[Weight])
RETURN
DIVIDE(SumWeightedValues, SumWeights)

Concrete example: if you have three rows with (Value, Weight) = (10,1), (20,2), (30,3), then SumWeightedValues = 10*1 + 20*2 + 30*3 = 140 and SumWeights = 6, resulting in Weighted Mean = 23.3333. Use DIVIDE to avoid errors if SumWeights is zero.

Step 3: Calculate the weighted variance

With the weighted mean available, calculate the variance by doing a SUMX of the squared differences weighted by Weight and divide by the sum of the weights. The population measure in DAX is:

Weighted Variance =
VAR Mean = [Weighted Mean]
VAR SumWeights = SUM(Sales[Weight])
VAR SumWeightedSq =
    SUMX(
        Sales,
        Sales[Weight] * (Sales[Value] - Mean) * (Sales[Value] - Mean)
    )
RETURN
IF(SumWeights = 0, BLANK(), DIVIDE(SumWeightedSq, SumWeights))

Returning to the example: the weighted squared differences would be ≈ 177.78, 22.22 and 133.33; sum ≈ 333.33; dividing by 6 yields variance ≈ 55.5556.

Step 4: Create the weighted standard deviation measure

The standard deviation measure is the square root of the weighted variance. In DAX:

Weighted StdDev =
VAR Var = [Weighted Variance]
RETURN
IF(ISBLANK(Var), BLANK(), SQRT(Var))

In the example, SQRT(55.5556) ≈ 7.4536. This value indicates the average dispersion of the observations when each is weighted by its respective weight.

Step 5: Sample adjustment (optional)

If you need a sample correction, you can use:

Weighted Variance Sample =
VAR Mean = [Weighted Mean]
VAR SumWeights = SUM(Sales[Weight])
VAR SumWeightedSq =
    SUMX(Sales, Sales[Weight] * (Sales[Value] - Mean) * (Sales[Value] - Mean))
RETURN
IF(SumWeights <= 1, BLANK(), DIVIDE(SumWeightedSq, SumWeights - 1))

This approach works well when weights represent integer counts. If weights are continuous weightings, consider the statistical interpretation before subtracting 1. In large datasets, the difference between using Σ wi or Σ wi - 1 is often small.

Verify the result

To validate the measures: 1) create a table visual with Value, Weight and the measures [Weighted Mean] and [Weighted StdDev]; 2) calculate manually for a small set (like the three-row example above) or use Excel/Python to replicate the calculation (in Python: numpy.average with weights and manual variance calculation); 3) test filters/contexts (by product, by month) and check that the measures respond correctly.

Practical tip: copy 10–20 rows to an Excel file and calculate the mean and standard deviation with manual formulas to compare. If you find discrepancies, confirm whether there are null weights, blank values or duplicate rows introduced by relationships between tables.

Conclusion

With these measures you have a robust way to calculate weighted standard deviation in DAX, suitable for various business scenarios. Sensible next steps: test performance on large tables (SUMX is an iterator — for millions of rows consider pre-aggregating), document the interpretation of the weights and decide between the population or sample version. Always check for divisions by zero and test with varied filters to ensure expected behavior in reports.