Weighted Standard Deviation

CHill97402

New Member
Joined
Apr 22, 2014
Messages
3
I am needing create a weighted standard deviation measure for a data set, and am having extreme difficulties determining how to achieve this with DAX.

We can assume a simple data structure, with the [Val] and [Wgts] columns holding the Values and Weights respectively.

Within Excel, assuming 'Vals' is a named range holding the values, and 'Wgts' is a named range holding the weights, the formula would be:

=SQRT(SUMPRODUCT(Wgts,((Vals-SUMPRODUCT(Vals,Wgts)/SUM(Wgts))^2))/SUM(Wgts)*COUNT(Vals)/(COUNT(Vals)-1))

How would I transcribe this into DAX?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

CHill97402

New Member
Joined
Apr 22, 2014
Messages
3
Thanks again, GDRIII - I read through that article earlier, however not sure there is much I can use for this application.

I did manage to get a measure that yields the proper value, however it is incredibly slow at calculating, likely due to it effectively generating the weighted mean for each row of data to calculate the variance. The formula is:

YSD (Q):=SQRT(SUMX(ADDCOLUMNS(ADDCOLUMNS(ADDCOLUMNS(Q,"Mean",Calculate([YMean (Q)],All(Q),Q[Q]=Earlier(Q[Q]),Q[nIter]=Earlier(Q[nIter]))),"Delta",(Q[YMean] - [Mean])^2),"wDelta",[Delta] * SWITCH([Weights],1,1,2,[Weight],3,[WW_Weight])),AVERAGE([wDelta]))/[Orgs (Q)])

A few things to call out on the data structure:

Q is the main table in question here. It has a simple structure of:

Q[Q] - String - A question number
Q[Iter] - numeric - Some questions have multiple iterations, so this is storing this
Q[Resp] - Numeric - A numeric response to the question
Q[Weight] - Numeric - One of the weights
Q[WW_Weight] - Numeric - Another set of weights
Q[YMean] - Numeric - An alternative response, and what the above metric is actually determining the SD on

[Weights] is just a simple measure that is linked to a slicer to select which weighting scheme to use (unweigthed, using Q[Weight], or using Q[WW_Weight].

Each row of Q is a question response for a single respondent of the survey. So to isolate to a particular question number, a filter on Q[Q] and Q[Iter] is needed (which is the point of the use of earlier() in the formula - it is restoring the filters from slicers on the output pivot/cubefunctions).

Unfortunately, I need to generate a couple of hundred of these values on each of the worksheets, and with the recalculating the weighted mean for each row, performance is prohibitively slow. There has to be a less 'loopy' solution! :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,038
Messages
5,856,980
Members
431,843
Latest member
Malahhai

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top