I’ve been trying to figure out how to write about this one, I think I finally have a simple enough scenario to describe: In my world of healthcare delivery, I have things like different payors where I want to know what % of the population is covered by a certain payor (like Medicare and Medicaid), and I don’t really need to show anything about the rest of the population other than have the raw numbers available in the computation. Using Superstore, we can do a equivalent modeling of that using Customer Segment as a stand-in for a set of possible distinct conditions for each patient (Customer). So I want to know what % of total Sales are in a given Segment, being able to filter for any set of Customer Segment(s) I want, and show the sum of the % of total Sales for only my filtered Customer Segments. Ideally ending up with something like this:

Read on for how this goes from relatively difficult in earlier versions to relatively simple in Tableau version 9.

In Tableau v8 and earlier, we can build a table showing the % of total for each Customer Segment in 7 clicks: 2 to select Customer Segment & Sales, one more to drop them in the data drop field area, 2 to turn on a % of Total Quick Table Calculation, and 2 more to get Column Grand Totals. One more click and I’ve got a Quick Filter on Customer Segment and boom, the view isn’t working right, the totals have changed (Consumer shows 34.33% instead of the accurate 19.42%) and the Grand Total is still 100%, not the 56.57% I’m expecting.

This often leads to a search on custom grand totals, and there we find that Tableau’s two-pass totals don’t work for table calculations. Or maybe we try going the table calculation filter route by creating a LOOKUP(MIN([Customer Segment]),0) calc and then find that it doesn’t affect the Grand Total because table calculation filters don’t affect the Grand Total computation. So the detail rows are accurate in this version, but the Grand Total still shows 100%.

The solution for this requires doing an aggregation of Sales to get the total Sales in such a way that it won’t be filtered out by any filters on Customer Segment. We could do that in the data source via a subquery, but then we can’t do other filtering in Tableau that would affect the subquery. Another idea would be to use a parameter, but parameters are a) only single-select and b) not dynamic, so that’s out. Another option is to use a Tableau data blend, where a duplicate connection to the data source has no linking field so it’s not affected by the filter on the primary source. Then we can write a calculation like SUM([Sales])/SUM([Sample – Superstore – English (Extract) 8.1 (copy)].[Sales]) and get an accurate total in both the Detail and Grand Total rows:

This isn’t too bad, however self-data blending can introduce a number of complications when there need to be filters on some fields but not others, when Filter Actions are involved (Filter Actions can’t target secondary data sources), when the number of distinct values of the the linking dimensions (aka the granularity of the blend) gets really large, etc. Data blending also requires a continuing awareness of what linking dimensions are in play to ensure accurate results and that can interrupt our analytical (or dashboard-building) flow. For example, if I swap Customer Segment for Order Priority in the self-blend then Tableau automatically picks up Order Priority as a linking dimension and all the % of total values in the detail rows are now way too large:

So what starts out as something that seems not too difficult gets complicated in a hurry.

Level of Detail Expressions to the Rescue

Tableau v9 adds a new option for aggregating across the data with Level of Detail expressions. In this case, I can write an LOD expression for % of total with SUM([Sales])/MIN({FIXED : SUM([Sales])}). The first SUM([Sales]) is computed at the given level of detail, so in the detail rows it’s computed for each Customer Segment and in the Grand Total across the entire data set (after any filters). The {FIXED : SUM([Sales])} is computed across the entire data set *prior* to any regular dimension filters, so it always gets the total. It needs to be wrapped in an aggregation to be the divisor, I chose MIN(), it could just as easily have been SUM(), AVG(), MAX(), or ATTR(). Then I can bring this into the view and use Customer Segment as a filter and have the desired results:

And even better, I can swap Customer Segment in and out with other dimensions and still have accurate results:

Conclusion

The key takeaway here is that while we can continue to use techniques like self-blending & table calculations to compute at different granularities, there are cases where replacing them with LOD expressions give us more flexibility and capability. The true reason why takes a bit of explaining: Tableau has essentially three levels of calculation – record level, aggregate, and table calc, that respectively allow more and more aggregation across the data in different ways while at the same time losing access to different bits of functionality. For example Filter Actions can originate from any level of calculation but can only target record-level fields. Table calculation filters aren’t respected by grand totals but filters on regular aggregates and dimensions are, and so on. The “side-along” nature of LOD expressions where they are computed as subqueries while respecting other filters and then returning results as record-level fields (that are then aggregated in the case of INCLUDE and EXCLUDE calcs) enables us to get the same results as self-data blends or table calculations while retaining all the functionality available to record-level fields.

So for me when I’m running into situations where in the past I would have used a self-data blend or a table calculation to get the desired results I’m now first checking out whether LOD expressions might give me the same computational outcomes with more ease of integration with other aspects of Tableau.

Here’s the LOD and Custom Grand Totals workbook on Tableau Public.