Weighted Standard Deviation in DAX (PowerPivot)

1
votes

I've been attempting to program a PowerPivot Workbook that I've been using to calculate a weighted standard deviation.

The problem is that when I use the code:

(the quality metric Q is weighted by the Product Tons for each record to get weighted statistics for variable periods [ie weeks, months, years])

Product Q-St.d:=SQRT((SUMX('Table',((([PRODUCT_Q]-[W_Avg_Q]))^2)*[TOTAL_PRODUCT_TONS]))/(((COUNTX('Table',[Production_Q])-1)*[Product Tons])/COUNTX('Table',[Production_Q])))

It calculates the [W_Avg_Q], which is the weighted average for Q, for each row as it iterates through instead of getting a weighted average for the whole context. I've learned pretty much all my DAX on the job or this site so I'm hoping there's some command to get the weighted average to calculate first. Does anyone know such a command? or another method of getting a weighted standard deviation out of DAX?

1
excelpowerpivotdaxstandard-deviationweighted

1 Answers

0
votes

I think what you want to do is to declare [W_Avg_Q] a variable and then use it in your formula.

Product Q-St.d :=
VAR WtdAvg = [W_Avg_Q]
RETURN SQRT((SUMX('Table',((([PRODUCT_Q]-WtdAvg))^2)*[TOTAL_PRODUCT_TONS])) /
    (((COUNTX('Table',[Production_Q])-1)*[Product Tons])/COUNTX('Table',[Production_Q])))

This way it gets calculated once in the proper context and then stored and reused within the formula.