4 Replies Latest reply on Mar 17, 2011 5:09 AM by Vas Itappsupport

Weighted Standard Deviation

Is it possible to calculate weighted standard deviation in tableau? Ultimately we’d like to be able to display weighted average and weight SD on a scatter plot to better represent the dispersion of data points of different weight.

Using a data set that has two dimensions (Region, Name) and two measures (Amount, Cheap Points), we want to calculate the weighted SD of the cheap points, with points with larger amounts having a greater weighting in the calculation:

(See formula in attached Formula.JPG)

We can correctly calculate weighted mean (Mu*) for the population and subsets (Region):

•    sum([Amount ]*[Cheap Points])/sum([Amount ])

The challenge now is to make Tableau use the appropriate Mu*, and count the relevant number of points for the weighted SD calculation. Please see the attached excel file with a sample dataset (columns a, b, c, d) that also include expected results, including weighted mean and weighted standard deviation (for total set, Region A and Region B).

Many Thanks.

• 1. Re: Weighted Standard Deviation

Here a workbook with two sheets, one that breaks the calculation into steps like you did in your excel workbook (thank you for the great details and example data), and the other sheet uses a single calculated field (with some optimizations built in, so it will continue to work with larger data).

• 2. Re: Weighted Standard Deviation

I created this workbook using version 6.0.5, and if you are using an earlier version, I would highly recommend you upgrade to the newest version. You can check your Help-About dialog box for your build/version number.

• 3. Re: Weighted Standard Deviation

Joe, thank you very much for the quick reply!

I get an exception error either when I try to load the twbx or if I "unpackage" the file, so I can't access the workbook. It is a generic exit error, can't really understand why it fails.

When I open it in notepad++ I can see your very interesting formulas and get an idea of the visualisations - could you please repost it or let me know if I need to repoint the source etc..

• 4. Re: Weighted Standard Deviation

I was using 6.0.2, that now works great. Many thanks!

(For new users: I downloaded it from https://licensing.tableausoftware.com/esdalt/)