8 Replies Latest reply on Aug 12, 2013 9:46 PM by Jonathan Drummey

# Calculating the Standard Deviation of the result of a table calculation

I have been struggling with this issue for a couple of days now and I am hoping to gain some insight from those more seasoned in Tableau than I am.

What I am wanting to accomplish seems straight forward so I am at a loss on why it appears to be so difficult. It is quite possible I am approaching it all wrong. I have tried to replicate the problem using the Superstore work book to hopefully spur some ideas or thoughts .Imagine that I needed to compute of the average of each category's total profit for each state and then compute the standard deviation of each states average.

Computing the average of each category's profit by state is pretty straight forward but when I go to compute the standard deviation I ended up with all NULLs or numbers that are way off.

I would welcome any insight that can be provided.

• ###### 1. Re: Calculating the Standard Deviation of the result of a table calculation

Arthur,

The workbook at this link calculates a standard deviation using table calculations.  Perhaps this will provide some insight.

http://community.tableau.com/docs/DOC-1429

Thanks,

Brad Llewellyn

Associate Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/in/bradllewellyn

http://breaking-bi.blogspot.com

• ###### 2. Re: Calculating the Standard Deviation of the result of a table calculation

Thanks Brad for the suggestion. Unfortunately it seems as if the workbook is corrupted. I tried to download it but I can’t get it to open without error.

• ###### 3. Re: Calculating the Standard Deviation of the result of a table calculation

Arthur,

What was the error message?

Thanks,

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/in/bradllewellyn

http://breaking-bi.blogspot.com

• ###### 4. Re: Calculating the Standard Deviation of the result of a table calculation

It was a brain cramp on my part.  I have Tableau 7 and it didn’t’ register to me that your workbook was in 8.  I have downloaded 8 and will take a look this afternoon.  Thanks for your help.

• ###### 5. Re: Calculating the Standard Deviation of the result of a table calculation

Thanks Brad for the example but I believe I am trying to do something a bit different.   In my case I am actually trying to calculate the standard deviation  on the result of a table calculation.  In my example this would be the standard deviation of each state's average category total profit.

So I would think I would have the following structure to my workbook:

1, Initial Aggregation to sum profit by each state/category

2. Table Calculation to calculate each states average the category total profit.

3. Second Table Calculation to compute the standard deviation of each state's average I can get through the first two steps just fine, but when I go to compute the standard deviation, I can't make Tableau use a single value for each state.  Instead the calculation either computes down including multiple values for each state or if I try to address by state I get a bunch of NULL.

I did try customizing my table calculation to use Lookup to identify if the row was the first in the state partition but that didn't seem to be working either.

Again any insights you may have would be helpful.

Thanks

Cliff

1 of 1 people found this helpful
• ###### 6. Re: Calculating the Standard Deviation of the result of a table calculation

Arthur,

You seem to be using the Superstore Sales sample data set.  Any chance you could post your workbook so that I can work with you?

Thanks,

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/in/bradllewellyn

http://breaking-bi.blogspot.com

• ###### 7. Re: Calculating the Standard Deviation of the result of a table calculation

I think was able to figure this out.  It was surprising straight forward.  Just took a bit to figure out exactly what Tableau was doing. As I show above the problem was that I had a record for each Customer Segment so the State Average was being included up to four times in the calculation.  To get around this I simply created an Index field that addressed on the Customer Segment. This then allowed me to filter the view for only those records with an Index == 0, thus only counting each State's Average once.

The only downside to this approach is that when the user a user views the underlying data, they are really don't get to see all the data.  The data is of course limited to the data for the first Customer Segment in the partition (Index == 0).

I would be curious what other approaches people may use to tackle similar scenarios.

• ###### 8. Re: Re: Calculating the Standard Deviation of the result of a table calculation

How I approach working with table calcs is doing the minimum necessary at each step of the way, here's how I altered  your workbook:

State Average calc: IF FIRST()==0 THEN WINDOW_AVG(SUM([Profit])) END. The Compute Using is set to Customer Segment (so it partitions aka restarts on each State). The IF FIRST()==0 reduces the results to just one per state.

Standard Deviation calc: PREVIOUS_VALUE(WINDOW_STDEV([State Average])) The nested Compute Using is set to State, Customer Segment. The PREVIOUS_VALUE causes the WINDOW_STDEV() calc to be computed only once, this improves performance.

I built the worksheet using Measure Names/Values.

Put a copy of the State Average Calc on the the Filters Shelf, filtering for non-Null values. This does the same as the Index filter you'd created, but is one fewer computation for the view.

Turn the tooltip off for Customer Segment, since it's unnecessarily confusing. (Customer Segment is on the Level of Detail to get the calculations to work).