The workbook at this link calculates a standard deviation using table calculations. Perhaps this will provide some insight.
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.
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.
1 of 1 people found this helpful
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.
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?
Associate Data Analytics Consultant
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.
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).