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:
Parameters.Here’s a how-to based on a recent request in the comments from my earliest custom Grand Totals post.
For example, the GROUP BY ROLLUP (SalesYear) statement only adds the grand total of all years to the result set but the GROUP BY ROLLUP (SalesYear, SalesQuartes) statement will add the following extra rows to the result set.Īs the last point, related to the ROLLUP extension, the subtotal combination will depend on the passed column The ROLLUP extension allows us to generate hierarchical subtotal rows according to its input columns and it also adds a grand total row to the result set. In order to calculate a subtotal in SQL query, we can use the ROLLUP extension of the GROUP BY statement. ROLLUP, CUBE, and GROUPING SETS are extensions of the GROUP BY statement and add the extra subtotal and grand total rows to the resultset. The GROUP BY statement is used to groups the rows that have the same values in a new summary row and it is the lead actor of the aggregate queries.