Question

How can I create a measure that calculates the weighted average of a column dynamically based on slicer selections?

I need a Power BI measure that calculates the weighted average of a specific column, where the weights and values change dynamically based on slicer selections. What is the best way to implement this using DAX functions like SUMX, DIVIDE, or CALCULATE to ensure accurate calculations?

Answer 1

Using SUMX and DIVIDE in DAX, a dynamic weighted average measure can be constructed in Power BI. The central theme is to compute the total weighted sum divided by the total weight while ensuring that the slicers' filters are applied.

Weighted average DAX formula.

Weighted Average = 
DIVIDE(
    SUMX( 'Table', 'Table'[Value] * 'Table'[Weight] ), 
    SUM( 'Table'[Weight] ), 
    0
)

Description:

SUMX('Table,' [Value] * [Weight]) → Multiplies each Value by the corresponding Weight and sums the products.

SUM('Table'[Weight]) → total weights in a dynamic manner as per depending on slicers.

DIVIDE(...) → Safe division is needed to avoid errors in cases where the denominator can probably be zero.

Essential Points:

This formula is responsive to the slicers and filters defined in Power BI.

If there are NULLs or zeros for weights, you can filter them out in the SUMX using FILTER().

For more complex scenarios, use CALCULATE() to scope the filter context.