Table Calculation Solution for Separate Custom Grand Totals for Rows and Columns Therefore we have to build our own calculations to do the custom Grand Total, in the rest of this post I show one option for table calculations and a second option that uses Level of Detail expressions. This is an unfortunate “feature” of Tableau’s two-pass totals. So even though it’s accurately calculating the average in the Column Grand Total it’s also returning a result that we can’t filter out in the Row Grand Total and that makes this unusable. I’ve duplicated the Two-pass Test measure here, the first one has the original Total Using Automatic that returns Nulls, the second has Total using Average and it’s now ignoring the MIN/MAX test and returning results in the Row Grand Total: However when we turn on two-pass totals this breaks. This calc works accurately when we first bring it into the view, it returns Null in the Row Grand Total: Here’s an example Two-pass Test calc, using the MIN/MAX technique from Customizing Grand Totals Part 2: IF MIN() = MAX() THEN Then the marks wouldn’t overlap and would be accurate. One thought is that we could take advantage of Tableau’s ability to put multiple pills on the Text Shelf, for example we could (theoretically) set up one calc to only return the sum of Sales in the Row Grand Total (and Null everywhere else), and then set up a second calc to return the Sum of Sales in the detail rows and then turn on two-pass totals for Average in the Column Grand Total. There’s no way to have the two-pass totals stay as the SUM() for the Row Grand total and Average for the Column Grand Total, so we need to build our own custom grand total. In the Columns the total is again based on the SUM(Sales) for each Priority, Ship Mode & Department, then averaged per Priority: When we turn on two-pass totals to do the average, in the Rows that is the SUM(Sales) for each Order Priority, Ship Mode, & Department then averaged per Ship Mode & Department. For the Columns, this is the SUM(Sales) for each Order Priority. For the Rows, this is the SUM(Sales) for each Ship Mode & Department. The default aggregation for the Grand Total is “Total using Automatic”, which aggregates at a higher level. With SUM(Sales) as the aggregate measure, we can turn on Grand Totals for Rows and Columns. The Problem or, Why We Have to Use a Custom Grand Total I’m going to use Superstore Sales, with Ship Mode & Department on Rows, and Order Priority on Columns:
We’ve got at least a couple of different solutions for this problem, in this post I’ll demonstrate one solution with table calculations that will work with any recent version of Tableau and another with Level of Detail expressions that will work in version 9 onwards.
The user asked how to have the Grand Total show a Sum of the measure for the rows and the Average of the measure on Columns, like so: Here’s a how-to based on a recent request in the comments from my earliest custom Grand Totals post.