Customizing Grand Totals – Part 3

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.

In November 2013 Tableau 8.1 also added a new two pass totals feature that may remove the need for customizing grand totals for your use cases, more details are in Tableau 8.1 Two Pass Totals. In December 2015 Tableau 9.3 added total control for placing totals to the left and/or on top of the view.

For this particular tutorial, you don’t actually need to know any SQL; however, learning SQL will provide a great boost to your analysis toolset. If you’ve never used SQL before or are not familiar with it enough to need a refresher, here’s a SQL Tutorial to get you started.

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]
  1. Copy the entire query – all the text from beginning to end.
  2. 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
  1. Press Enter to create a new line.
  2. Paste in the text you copied in step 5.
  3. 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]
  1. 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$]
  1. 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:
Debugging errors in SQL syntax can be a great learning experience, particularly learning how to deal with tremendous frustration, anger, powerlessness, and hopelessness as you go through all of your code ten times over only to find that it’s failing due to a missing or misplaced comma, parenthesis, or square bracket. I wish I could provide you with a guaranteed method for making this work, but I can’t. I can only hope that your persistence and perseverance will be rewarded, in this life or the next one.
  1. Once you’ve got the query working, press OK to close the Edit Custom SQL dialog.
  2. Press OK to close the Edit Connection dialog. You are returned to the Tableau main window, but now with twice the data.
This is a more complicated SQL query than necessary to return this particular data set, I set up these instructions because they can consistently work even after other customizations have been made to the SQL, such as if you’ve set up the data source using the Multiple Tables option.

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

As of Tableau v9.2 this is no longer needed, you can get the totals on top or left using total control.

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:

Note that you are limited to only showing subtotals for one level with this method.

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.

As much as I enjoyed writing this blog post, it wouldn’t have been needed if there was more control over how Tableau calculated totals. Here’s an Idea on the Tableau forums for this very feature: Totals and subtotals over displayed marks instead of separate calc.

Here’s the workbook on Tableau Public.

22 thoughts on “Customizing Grand Totals – Part 3

  1. Joe Mako

    This is an incredible article, thank you for taking the time to put this together. This is the most flexible method for gaining exact control over the Grand Total. The content you have on this blog is excellent.

    Additionally, you can extend this method to get your data aggregated at multiple levels, or computed in different ways in the same view by using the self union multiple times. For example, if you want to show monthly, quarterly and year or different combinations of actual and budget. This method many different possibilities. There are also other interesting things that can be accomplished with a self union as well when combined with table calculations, eg filter logic like “Has Only”, “Does Not Have”, or “Does Not Have Only”. If you can transform your data prior to Tableau, and this custom SQL is a data transformation prior to Tableau, then the possibilities are nearly endless.

    Reply
  2. Vusi

    Thanks so much for this article. It opened up my eyes to a simple way of achieving something I have considered quite difficult to do in the past.

    Reply
  3. Pingback: Customizing Grand Totals – Part 1 | Drawing with Numbers

  4. Pingback: Customizing Grand Totals – Part 2 | Drawing with Numbers

  5. Andy Piper

    Great series. “Part 2” just saved the day to correct a grand totals issue I was experiencing summing INT values. I hope to look more into your “Part 3” as time permits.

    Reply
  6. Esther

    Hi Jonathan, i am really happy finding ur post about customizing Grand Total in Tableau. Especially this Part#3 which is really what i need.

    The problem with the Part#3 is i cannot hide and unhide the hierarchy, because every sub-category is put in different calculated field. And we cannot insert the hierarchy in calculated field.
    Do you have some work-around for this too?

    Reply
    1. Jonathan Drummey Post author

      Hi Esther,

      Take a look at the Hierarchy worksheet, I think it has what you are looking for. If now, can you be more specific about what you are trying to do?

      Reply
  7. Esther

    Hi Jonathan,

    Thanks for your clue. I found what i need.
    Basically i want to expand the hierarchy with the customized Grand Total.

    Reply
  8. Matthew

    Jonathan, your work here is highly appreciated. I have really enjoyed learning how to overcome some of the grand-total-obstacles that I’ve been facing for some time.

    What I have found though is that when I use the table calc method with a copy dimension on the level of detail shelf, the formatting in the pane becomes left aligned and there seems to be nothing that can be done to shift this.

    I guess I should be happy with grand totals working the way I want them but left aligned numbers can be distracting. I notice your screen shots don’t have this issue, so I’m trying to figure out what I’m doing wrong. I’ve tried this in versions 8, 8.1, and 8.2 but get the same problem. Any ideas?

    Reply
  9. Matthew

    Hi Jonathan,

    That’s spot on. It solves the problem perfectly.

    I was amused by your comment on the dilemma of wishing that workarounds weren’t necessary, but enjoying them at the same time!

    I appreciate your reply.

    Reply
  10. Stanislava Tropcheva

    Really great article!!!
    I read it more than twice, but eventually understood the logic. The problem was I wanted more totals in the viz and no subtotals if the sub-category is only one. I used the logic above and made calc field which gives me the results I need (custom subtotals for sub-categories , Tableau totals for categories and Tableau grand total).
    Thank you for sharing!

    Reply
  11. Pingback: Custom Totals in Tableau (with the help of Customizing Grand Totals – Part 3 by Jonathan Drummey) | Stanislava's Blog

    1. Jonathan Drummey Post author

      Hi Debbie, this post was written when Tableau had a dialog for single table vs. multiple tables connections. The new data source window introduced in v8 supports dragging and dropping tables so it integrates single and multiple tables connections.

      I haven’t worked with Netezza, but Tableau’s documentation indicates that you can join multiple tables.

      Reply
    1. Jonathan Drummey Post author

      This is not a built-in feature of Tableau. The only way I can think of might be more trouble than it’s worth. If you are using the duplicated data technique outlined in the above post to add additional rows to your data source for subtotals then you could set up a calculation that would suppress the single rows.

      Jonathan

      Reply
  12. Nikhil Sahni

    Hi Jonathan,
    I am using Tableau 9.3. Would you please explain how can I use custom sql for my excel data source. I don’t see the things in my version when I follow your steps.

    Reply
  13. Pingback: Tableau – shreedhar

Please add your thoughts and perspectives