Embarrassment is the feeling of getting caught doing exactly what you wanted to be doing.
– Author unknown
Today I get to celebrate a new Tableau 8.1 feature and reveal some obsessive compulsive behavior. My first big set of posts on this blog were about answering a really common forums question, how to customize grand totals. With Tableau 8.1’s new Two Pass Totals feature, you just might not need those posts anymore!
The new two pass totals let us have a custom aggregation of Sum, Avg, Min, or Max over an aggregation, or keep the current “Automatic” behavior. Here’s a quick example with a simple worksheet from Superstore Sales:
In the Grand Total, the sum of sales is computed across all of the data. If we want the Grand Total to be the average of the sum of sales for each department, we can get via two different ways in a couple of mouse clicks:
- From the Analysis->Totals->Total All Using->Average menu (this sets the aggregation for all measures in the view):
- Or from the SUM(Sales) pill itself, by right-clicking on the pill and using the context menu:
What Types of Measures are Supported by Two Pass Totals in v8.1
For the Tableau 8.1 release, only certain types of measures are supported for two-pass totals:
- regular aggregates like SUM([Sales]) from primary and secondary data sources
- computed aggregates like SUM([Profit])/SUM([Sales]) from primary or secondary data sources
What is not supported in v8.1 for custom two-pass grand totals are the following:
- computed aggregates that include fields from blended data sources such as SUM([secondary data data source].[Sales])
- table calculations from any source
If you’d like to have those options available in a future release, please vote up http://community.tableausoftware.com/ideas/1232. In the meantime, you can still use the techniques from the custom grand totals post I linked to earlier.
When an Aggregate is an Aggregate
One interesting wrinkle where the custom calculations and two pass totals have an interesting reaction is when using the MIN/MAX technique from Customizing Grand Totals Part 2 with a regular aggregate calc. For that calc, Tableau can compute the two pass totals for Sum, Avg, Min, and Max custom grand totals and thereby override the custom aggregation. Here’s an example, from left to right the Grand Total aggregations are Automatic (which returns the custom grand total of -1000), Sum, Avg, Min, and Max:
OCUCD – Obsessive Compulsive Use Case Design (or Disorder)
During the Tableau 8.1 beta process I tested this out on most every use case I could think of – there are 44 of them. Here’s the workbook I used during the beta on Tableau Public:
If you come up with other use cases, or find other interesting uses of this new functionality, please share them!