Stumped by a blending problem where the column total for a blended calculation was zero, I called on my friend Joe Mako.
Exceptionally generous with his time & knowledge, Joe helped me to understand: what at first had seemed to be a simple confusion was actually various roadblocks in Tableau that each require some effort to understand & work around.
Summarizing the knowledge I received from Joe, I'd like to thank him again for his generosity! All of the packaged workbooks are attached to my forum question, which is here:
Think of Totals as a ~Separate Sheet
The first bit of insight is that column totals can generally be thought of as a semi~separate worksheet. This is especially true for automatic totals.
In many ways, automatic column totals behave like a TOTAL() calculation. That is, they are performed on the server (inside of the data source). And as a result, they explicitly ignore the dimensions on the rows shelf of your worksheet.
This is an important concept, one that plays into the solution and one that Curtis Harris has also hinted at with his clever method for improving the grand totals on a bar chart.
Notice how Jim Wahl's comment at the bottom of that post also speaks to the very same details we're discussing here:
The other way determine if you're on the grand total row is
IF MIN(Category) <> MAX(Category) THEN SUM(Sales) END.
This works because Tableau removes the Category dimension when calculating the grand total.
I think I like your approach of using SIZE() = 1 better, because I can change the dimensions without changing the calc.
Crux of the Issue
So, the "automatic" column totals are computed on the server (inside of the data source), and they ignore the dimesions on the rows shelf. What's required then is a different type of column total, one that computes locally & takes those dimensions into consideration (similar to a table calculation).
Enter Total Using
In fact, Tableau has partially implemented this feature already, it's called Total Using. Here is a quick look at how it works:
That's great. But as we'll see, the crux of my problem was that "Total Using" isn't always available. Joe me showed two reasons why, because I had managed to stumble upon both of them.
1. Calcs Using ATTR
Before we even get into data blending, the first reason why Total Using can be unavailable happens within a single data source. It's simply because the calculation involves an ATTR( ).
Just to show you what I'm talking about, in the screenshot below I've simplified the calculation to only use values from the primary data source. As you can see, by manually imposing the same logic as an ATTR, IF MIN() = MAX(), the ability to "Total Locally" "Using SUM" magically becomes available. And the column total works correctly when we do.
In the calc that uses ATTR(), the only option available for "Total Using" is "Automatic". And there's no love from the automatic column total.
2. Calcs Across the Blend
But my calculation really does need a value from the secondary data source. And this "calculating across the blend" failure is the real reason why my column total was zero. Total Using isn't available for pills that compute across the data blend.
These are complex pieces of code from the Tableau engineers, and technical challenges likely exist. But from an end user perspective these limitations seem arbitrary.
A work around is possible, but it's also rather painful (see below). So it would be best if Tableau can extend the current implementation of Total Using to completion. A column grand total should be easy, especially in an Enterprise business world that grew up on Excel.
Forced to Automatic
OK! So here is the reason why my blended boolean column totals were zero:
Since the pill being totaled computes across the blend, the column grand total is forced into an "Automatic" Total Using. And the "Automatic" Total Using behaves much like a regular old TOTAL() computation, which performs inside of the data source and as a result it ignores the dimensions that partition the rows.
Since my linking field to the secondary data source just happens to be one of those dimensions on the rows of the canvas that is being ignored, the "semi~extra sheet" that computes the column total is literally ignoring the linking field for the blend! No wonder things break down.
The setup for the workaround then is to find a way to prevent the column total from ignoring the linking field for the blend. Joe's answer: link the blend on a completely separate field.
Merely duplicating the existing pill is not enough. We need a completely separate pill, but one that utilizes the same calculation.
As you can see below, this is only as easy as 1-2-3-4. Some other breakable things get messed up and also need to be fixed along the way.
- We blend on a twin of the linking field, which allows for the automatic column total to avoid ignoring the blend
- But doing so wreaks havoc in the total by "overlapping values"
- To fix this, an IF FIRST() = 0 THEN WINDOW_SUM([AGGREGATION]) END, which computes along the new linking pill to take advantage of the blend, will prevent the overlapping
- This is effectively the same as Tableau's Total Using
- For example: replacing the WINDOW_SUM with WINDOW_AVG accomplishes the same thing that Total Using "Average" would
- And moreover, the variety of WINDOW_AGGREGATION table calcs available offer many more aggregation options than Total Using currently does (such as median, count, percentile, standard deviation, variance, etc).
- Use an Alias to beautify the now terrible column header
The Final Solution
Assuming these column totals are truly so important for what you're trying to do that you must have them, then the final solution is to do all of the above & create an IF FIRST() == 0 THEN WINDOW_SUM([AGGREGATION]) pill for every measure of your crosstab. This prevents the overlapping text for each of them. But it also messes up the column headers, and to fix those you must right-click on the header to add an alias.
Thanks to Joe! It's really nice to understand the inner workings of these cross blend problems. Understanding what's going on "under the hood" is the best way to flow with Tableau and avoid getting frustrated.
But at the same time, wouldn't it also be nice if the Tableau engineers would build out the rest of the Total Using functionality? Shouldn't everything just work as expected, even across the data blend?
Along those lines, Jonathan Drummey has an idea:
Thanks again, Joe!
Word Count: 1,150
- "Blended Boolean Column Totals are Not What They Seem", Tableau Community Forum Question, January 18, 2015
- "Tableau Quick Tricks - Building A Better Bar Chart", Curtis Harris, curtisharris.weebly.com, January 5, 2015
1."Computing Totals", Tableau Software Quick Start Feature Guides, January 23, 2015
- "Configuring Grand Totals", Tableau Software Online Help, January 23, 2015
- "ATTR() - Tableau's Attribute Function Explained", Tim Costello, Interworks Blog, May 15, 2014
- "Total Using across the data blend", Keith Helfrich, Tableau Community Forum Ideas, January 23, 2015