In Part 1 and Part 2 of this series, I described how Tableau computes Grand Totals and several options for generating your own Grand Totals. In this post, you’ll learn the most flexible method for customizing Grand Totals, via custom SQL to duplicate the data.

The advantages of the custom SQL method for computing Grand Totals are the following:

• Hierarchies can be expanded and collapsed, which is not possible within the worksheets using the other methods described in Part 1 and Part 2.
• A limitation of the MIN/MAX and table calc methods from Part 2 was that when calculating subtotals, they can not detect when there is only one member of the dimension being subtotaled, so return incorrect results. The custom SQL method is not affected by this.
• You can locate the total row on the top, or total column on the left, which is not possible in Tableau’s default calculations or the other methods. This is no longer necessary with Tableau 9.2 and up since we now have the total control feature.
• There is complete control over the total calculations, because you are doing them yourself instead of relying on Tableau.

The Custom SQL method has some disadvantages:

• Every view must be filtered and/or measure must be customized to accurately display correct values.
• The data is duplicated, which can affect disk and memory requirements, and performance.
• Tableau offers a separate formatting for the built-in Grand Total header – by default, it’s a bold face version of the header text, this is not possible using custom SQL.

How the Custom SQL Method Works

Back in Part 2, we learned that Tableau internally performs a separate set of computations to compute grand totals. In the Custom SQL method, we set up the data to allow us to explicitly do the same:

1. Duplicate the data via Custom SQL using a UNION query and add a “Copy” dimension to indicate whether the copy is “Data” or “Total”.
2. Create calculated fields to return the desired measure when the Copy is “Data”, and a separate measure when the Copy is “Total”.
3. Create views that include the Copy dimension and the calculated fields.
4. Amaze your co-workers as you can now casually drop phrases like, “To create that view I simply used Custom SQL and a UNION query to duplicate the data, then generated my own custom aggregations for the grand totals.”

Why We Need Custom SQL

Tableau provides a graphical interface to link – more commonly known as “join” – data from one table to another, in the Data->[Your data source]->Edit Connection… dialog, using the Multiple Tables option:

The supported join methods are Inner, Left, and Right.

• Inner joins only return rows that have matching values in both tables
• Left joins return all rows from the left table, and any matching rows from the right
• Right joins return all rows from the right table, and any matching rows from the left

In our case, we want to have all of the rows, and have a copy of the rows as well, so it’s not a matter of linking tables as in a join but returning all the results of two queries together. In SQL, this uses a UNION operator. The UNION operator simply merges the results of two (or more) separate queries. Here’s an example:

SELECT *, "Data" AS [Copy] FROM [Orders$]
UNION ALL
SELECT *, "Total" AS [Copy] FROM [Orders$]

The first line gets all the columns – that’s the “*” – from the Orders table, and adds a “Copy” column with the value “Data” for every row, then the third line gets all the columns from the Orders table again, adding a “Copy” column with the value “Total” for every row. The UNION ALL in the middle merges the two result sets together.

Setting Up the UNION Query

Here’s how to set up the UNION query:

1. Either use the Data->[your connection]->Edit Connection… menu item or right-click on your data connection and choose the Edit Connection… context menu item. The Edit Connection… dialog will open:
2. Click on the Custom SQL radio button. You will now see either a bunch of SQL code or what looks like gobbledygook until you’ve learned SQL:
3. Click the ellipses button […] to the right of Custom SQL. The Edit Custom SQL dialog opens:
4. On the line just above the FROM line of the SQL query, add a comma “,” at the end of the line, then press Enter to create a new line. Type in the following text on the new line:

"Data" AS [Copy]

5. Copy the entire query – all the text from beginning to end.
6. Move down to the very end of the query, and after the FROM line press Enter to create a new line and enter the following:

UNION ALL

7. Press Enter to create a new line.
8. Paste in the text you copied in step 5.
9. Scroll down to the bottom, and just above the FROM line replace the “Data” in “Data” AS [Copy] with “Total”, so that row looks like this:

"Total" AS [Copy]

10. When using the Superstore Sales data, your query will now look like the following:

SELECT [Orders$].[City] AS [City],
  [Orders$].[Customer Name] AS [Customer Name],
  [Orders$].[Customer Segment] AS [Customer Segment],
  [Orders$].[Discount] AS [Discount],
  [Orders$].[Order Date] AS [Order Date],
  [Orders$].[Order ID] AS [Order ID],
  [Orders$].[Order Priority] AS [Order Priority],
  [Orders$].[Order Quantity] AS [Order Quantity],
  [Orders$].[Product Base Margin] AS [Product Base Margin],
  [Orders$].[Product Category] AS [Product Category],
  [Orders$].[Product Container] AS [Product Container],
  [Orders$].[Product Name] AS [Product Name],
  [Orders$].[Product Sub-Category] AS [Product Sub-Category],
  [Orders$].[Profit] AS [Profit],
  [Orders$].[Region] AS [Region],
  [Orders$].[Row ID] AS [Row ID],
  [Orders$].[Sales] AS [Sales],
  [Orders$].[Ship Date] AS [Ship Date],
  [Orders$].[Ship Mode] AS [Ship Mode],
  [Orders$].[Shipping Cost] AS [Shipping Cost],
  [Orders$].[State] AS [State],
  [Orders$].[Unit Price] AS [Unit Price],
  [Orders$].[Zip Code] AS [Zip Code],
  "Data" AS [Copy]
FROM [Orders$]
UNION ALL
SELECT [Orders$].[City] AS [City],
  [Orders$].[Customer Name] AS [Customer Name],
  [Orders$].[Customer Segment] AS [Customer Segment],
  [Orders$].[Discount] AS [Discount],
  [Orders$].[Order Date] AS [Order Date],
  [Orders$].[Order ID] AS [Order ID],
  [Orders$].[Order Priority] AS [Order Priority],
  [Orders$].[Order Quantity] AS [Order Quantity],
  [Orders$].[Product Base Margin] AS [Product Base Margin],
  [Orders$].[Product Category] AS [Product Category],
  [Orders$].[Product Container] AS [Product Container],
  [Orders$].[Product Name] AS [Product Name],
  [Orders$].[Product Sub-Category] AS [Product Sub-Category],
  [Orders$].[Profit] AS [Profit],
  [Orders$].[Region] AS [Region],
  [Orders$].[Row ID] AS [Row ID],
  [Orders$].[Sales] AS [Sales],
  [Orders$].[Ship Date] AS [Ship Date],
  [Orders$].[Ship Mode] AS [Ship Mode],
  [Orders$].[Shipping Cost] AS [Shipping Cost],
  [Orders$].[State] AS [State],
  [Orders$].[Unit Price] AS [Unit Price],
  [Orders$].[Zip Code] AS [Zip Code],
  "Total" AS [Copy]
FROM [Orders$]

11. Click the Preview Results button to verify that you entered the query correctly – Tableau will return the first 10,000 rows of the query. If you get the dreaded “An error occurred while loading the data.” message, click the Show Details button to get a possibly-helpful-but probably-uninformative error window, like the following message that appears if a comma was not entered as described in step 4:

12. Once you’ve got the query working, press OK to close the Edit Custom SQL dialog.
13. Press OK to close the Edit Connection dialog. You are returned to the Tableau main window, but now with twice the data.

Building Your First View

Since all rows of data have been duplicated, it’s vital to be careful to not have too much data in the view. For example, if we drag Sales onto the Text Shelf in an empty view in the view, the result is $29,831,202:

However, when we filter for just one data set, the result is the (correct) $14,915,601:
Every view will need to have a filter applied, do the filtering within calculations, or have Copy in the view:
Now, let’s duplicate a basic total – sum of sales per category, with a grand total to be the sum of all sales. If we just drag Category onto the Rows Shelf, we get duplicated rows:

This is where we take a different tack from the prior methods of customizing grand totals. Instead of creating a different measure to return different values, we create a discrete calculated field (a dimension) to make Tableau only draw the headers that we want, and then the sum of sales measure will return different values for each header. Since we want to total up by Category, here’s the Category Header formula, which will return the “Category” for the Data values of Copy and “Grand Total” for everything else.

IF [Copy]="Data" THEN [Category] ELSE "Grand Total" END

Put Category  Header on the Rows Shelf, Sort it by Copy/Min/Ascending, Sales on the Text Shelf and now you’ve got a custom Grand Total:

The reason for the sort is that since all the values of Category and the “Grand Total” value are in the same dimension, by default they will sort alphabetically.

Now, there’s nothing to distinguish the Grand Total row from the other rows in the worksheet, so this view isn’t as legible as the default view. Unfortunately, we don’t have control over the Grand Total font the way we can in the default view; however, there are a few different formatting tricks you can use:

Adding Borders

Drag the Copy dimension to the left of the Category Header on the Rows Shelf, then right-click on the Copy blue pill and turn off Show Header. Tableau’s default border settings will add a border:

Change shading

You can also change the shading so the Grand Total row is shaded and other rows aren’t. Go to Format->Shading to open the shading window, then in the Row Banding section drag the Level slider to the left:

Totals on the Top and Side

Two layouts that are possible using the Custom SQL Grand Total are having the totals on top or on the left. To put the total on top, simply change the Sort on  either the Copy or the Category Header. For the Total on the Left, move the Copy and Category Header to the Columns Shelf:

Expanding and Collapsing Hierarchies

Another possibility enabled by the Custom Grand Total is that you can set up Tableau hierarchies and use them, while retaining an accurate Grand Total. This is not possible using the previous methods. For example, we can set up a Sub-Category Header that has much the same formula as the Category Header:

IF [Copy]="Data" THEN [Sub-Category] ELSE " " END

It’s returning a ” ” (space) instead of “Grand Total” so that we’ll only show the “Grand Total” text from the Category Header. Then a hierarchy can be created with the Category Header:

And a view can be set up:

Different Aggregations for Grand Total Row vs. Data Row

A common request is to have the Data rows show one aggregation, then the Grand Total row shows a different aggregation. For example, we can set up a calculation to return the average Sales per Category for the data rows, and the sum of Sales for the Grand Total row with this formula:

IF ATTR([Copy]) = "Data" THEN
    AVG([Sales])
ELSE
    SUM([Sales])
END

The original example used in Parts 1 and 2 was to show the sum of the average of Sales per Category, so the Grand Total row is a visual sum of the column.  This requires a table calculation somewhat like the one used in part 2. The formula is:

IF ATTR([Copy])="Data" THEN
    AVG([Sales])
ELSE
    IF FIRST()==0 THEN
        WINDOW_SUM(AVG([Sales]), 0, IIF(FIRST()==0,LAST(),0))
    END
END

Then in the view, Category is put on the Level of Detail Shelf, and the Compute Using is set to Category:

Adding additional levels is also possible. Create additional “[dimension name] Header” values to use on the Rows or Columns Shelf, and then use the original dimensions on the Level of Detail, with an Advanced… Compute Using set to all of the dimensions on the Level of Detail:

Adding Subtotals

To work with subtotals, instead of the Copy dimension being used to generate the Grand Total value, the Copy dimension is used to generate the sub totals and we use Tableau’s Grand Total for the final row. The following example also uses the sum of averages sales, and the calculation is a bit simpler because we don’t need the top level Category dimension on the Level of Detail Shelf. Like the above, the Compute Using for the measure is set to only the dimension(s) on the Level of Detail:

Excluding Results from a Total or Detail

When we have more control over the grand total as a separate calculation, we can do things like only include or exclude certain results from the total row. This is also possible using the other custom grand total methods. Here’s a sample calc that calculates the average sales, but excludes that in the grand total row:

IF ATTR([Copy])="Data" THEN
    AVG([Sales])
ELSE
    AVG(IF [Category] != "Technology" THEN [Sales] END)
END

Conclusion

Once you know how to use Custom SQL to duplicate your data, many possibilities open up for calculating and arranging total and grand total rows beyond Tableau’s built-in capabilities. If you have any other ideas, please add them to the comments below.

Here’s the workbook on Tableau Public.