Custom Totals in Tableau (with the help of Customizing Grand Totals – Part 3 by Jonathan Drummey)

Have you ever wondered what to do if Tableau’s ability to show you totals and subtotals doesn’t work the way you want?
I suggest you to read the following article:
Customizing Grand Totals – Part 3

and then continue reading this one.

My aim was to create a viz, showing the subtotals for sub-categories (Product Type), totals for Categories (Product Line) and grand total for all of the data with the additional condition that if the sub-category has only one record (Product) to exclude the subtotal for that sub-category.
In my example I filtered the data (Coffee sub-category has only “Delaf Irish Cream” item) to show this scenario.

So I started following the very best steps in Customizing Grand Totals – Part 3 (I also recommend you to read the previous two parts.).
Jhonatan Drummey said in Customizing Grand Totals – Part2: “Just like the MIN() and MAX() technique, the calculation will fail when using subtotals and there is a single value for a dimension in the subtotal.” Part 3 opened my eyes how to fix this problem:

1. First set up an union query (As you see I use Tableau’s sample data source – “Sample – Coffee Chain (Access):

2. Create two hierarchies:

Products Hierarchy:

Product Line – Category
Product Type – Sub-Category
Product

Product (Headers) Hierarchy:

Sub – Category Header (Product Type):

Notice that we have additional condition in the else – clause in the Case statement:
blank if the product is “Decaf Irish Cream” (Don’t exclude it!).

Product Header (Product):

3. Create your viz:
Filter by [Product]. We only want to see data for “Decaf Irish Cream”:

Drag [Product Line], [Product Type], [Sub – Category Header (Product Type)] and [Product Header (Product)] onto Rows and Hide [Product Type].

Sort [Sub – Category Header (Product Type)] Manual:

And now – the funny part – Create a calculated field for Sales!

There are 3 main levels in the formula:
For data rows
For Subtotals
Tableau’s totals and Grand Total


Drag the [Sales (Custom)] calculated field onto Text and choose Compute using Pane Down.

You can download the workbook here.

One Response to Custom Totals in Tableau (with the help of Customizing Grand Totals – Part 3 by Jonathan Drummey)

  1. naveena says:

    Hi,

    This is a really good post. I am having a similar issue where the subtotals don’t update based on the filter/parmeter. I was trying to use this logic it still did not work. below is the link to my questions in the forum

    http://community.tableau.com/message/394202?et=watches.email.thread#394202

    can you let me know if there is something i am missing in my calc? appreciate your help. TIA

Leave a Reply

Your email address will not be published. Required fields are marked *