Calculate Weighted Average and Weighted Standard Deviation in DAX

0
votes

This is something I have never attempted before

I want to calculate the weighted standard deviation and the weighted average for the dataset containing records for actual values measured against set values

The calculation is to be done using a DAX query in PowerBI

Set Value 1 Actual Value 1 Set Value 2 Actual Value 2
10 8 101 102
10 11 101 104
10 12 101 97
10 7 101 99
10 13 101 97
10 13 101 100
10 9 101 98
10 10 101 100
10 8 101 102
10 14 101 98
10 8 101 98
10 13 101 96
10 13 101 103
10 14 101 102
10 7 202 205
20 18 202 198
20 18 202 197
20 19 202 203
20 19 202 202
20 19 202 201
20 22 202 202
20 18 202 200
20 17 202 195
20 23 202 198

Edit 1: Please use the data above. Also, please note that although, set points are what we intend to use as weights but its the count of a particular set point. for eg: if the setpoint1 10 is repeating 15 times and set point1 20 is repeating 9 times then wieght to be used as 15 & 9 respectively

1
powerbidaxpowerbi-desktop
Please post your expected output as well – Arun Palanisamy
can you post your sample data as a machine readable table? It's enough to copy paste the table from power bi to this site thisdavej.com/copy-table-in-excel-and-paste-as-a-markdown-table and then to the question – sergiom

1 Answers

1
votes

Weighted Average and Standard deviation can be implemented in DAX according to their mathematical definition.

Assuming we have a table with the columns Weight and Value the formula for the Weighted Average is

WAvg = 
VAR Num = SUMX( Samples, Samples[Weight] * Samples[Value] )
VAR Den = SUM( Samples[Weight] )
RETURN DIVIDE( Num, Den )

and the formula for the Weighted Standard Deviation is

WStdDev = 
VAR WAvg = [WAvg]
VAR Num = SUMX( Samples, Samples[Weight] * (Samples[Value] - Wavg)^2 )
VAR Den = SUM( Samples[Weight] )
VAR WVar = DIVIDE( Num, Den )
RETURN SQRT(WVar)

Edit: if I understand your new request, the Weight is the number of rows with the same Set Value, that is to be used for each of the Actual Value. Then, since there are two pairs of columns, I assume that the requirement is to have a set of measures per each couple of columns.

The formula requires to add a count of the number of rows per each group of Set Value, to be used as weight. I imported the sample table as table "V"

Weighted average for Set Value 1 and Actual Value 1

WAvg1 = 
VAR Num =
    SUMX(
        ALL( V ),
        CALCULATE( COUNTROWS( V ), ALLEXCEPT( V, V[Set Value 1] ) ) * V[Actual Value 1]
    )
VAR Den =
    SUMX(
        ALL( V ),
        CALCULATE( COUNTROWS( V ), ALLEXCEPT( V, V[Set Value 1] ) )
    )
RETURN
    DIVIDE( Num, Den )

Weighted average for Set Value 2 and Actual Value 2

WAvg2 = 
VAR Num =
    SUMX(
        ALL( V ),
        CALCULATE( COUNTROWS( V ), ALLEXCEPT( V, V[Set Value 2] ) ) * V[Actual Value 2]
    )
VAR Den =
    SUMX(
        ALL( V ),
        CALCULATE( COUNTROWS( V ), ALLEXCEPT( V, V[Set Value 2] ) )
    )
RETURN
    DIVIDE( Num, Den )

Weighted standard deviation for Set Value 1 and Actual Value 1

WStdDev1 = 
VAR Num =
    SUMX(
        ALL( V ),
        VAR WAvg = [WAvg1]
        RETURN
            CALCULATE( COUNTROWS( V ), ALLEXCEPT( V, V[Set Value 1] ) ) * ( V[Actual Value 1] - WAvg ) ^ 2
    )
VAR Den =
    SUMX(
        ALL( V ),
        CALCULATE( COUNTROWS( V ), ALLEXCEPT( V, V[Set Value 1] ) )
    )
VAR WVariance =
    DIVIDE( Num, Den )
RETURN 
    SQRT( WVariance )

Weighted standard deviation for Set Value 2 and Actual Value 2

WStdDev2 = 
VAR Num =
    SUMX(
        ALL( V ),
        VAR WAvg = [WAvg2]
        RETURN
            CALCULATE( COUNTROWS( V ), ALLEXCEPT( V, V[Set Value 2] ) ) * ( V[Actual Value 2] - WAvg ) ^ 2
    )
VAR Den =
    SUMX(
        ALL( V ),
        CALCULATE( COUNTROWS( V ), ALLEXCEPT( V, V[Set Value 2] ) )
    )
VAR WVariance =
    DIVIDE( Num, Den )
RETURN
    SQRT( WVariance )

Applying these formulas to the sample table we get these results

visual card with results