Tableau - Total one dimension in separate column 

Thursday, October 13, 2016 3:24:53 PM

Well, many of us come to Tableau after being frustrated with Excel.  Then, to our surprise, Excel does some things easily that Tableau doesn't seem to handle well.  There are lots of posts concerning how to create subtotals and grandtotals that are customized to a particular need.  However, the problem that I had was that I wanted the Totals for a particular dimension to show as a single column -- that is, if using something like Superstore Sales, I want a crosstab that has Region as the first column discrete dimension, then year(Ship Date) as the sub-column.  Relatively simple, until I wanted to see the order of the Columns to be like this:
 

Total Central East
2011 2012 2013 2014 2015 2011 2012 2013 2014 2015 2011 2012 2013 2014 2015
                             

I only show two of the Regions, but the idea remains -- how can I get the Years' Total to show up in this fashion within Tableau?

Well, the answer is in custom SQL -- I union the source table with itself, substituting the word 'Total' for the Dimension that is listed at the top -- in the above example, that would be Region.  So, using Superstore Sales, which is an Excel file, that means using the legacy connection so that Custom SQL can be created.

Basic overview of SQL:

Select dimension1, dimension2, dimension3, measure1 from Source
Union
Select 'Total' as dimension1, dimension2, dimension3, measure1 from Source

What this does is adds a 'Total' value to all the rows for that one selected dimension, so that when the dimension is selected for a crosstab, not only are all the values present, but there is a new value also available - called 'Total'.  Using Sum on the measure will result in correct values for all the pieces and the Total, which then can be broken down by any of the other dimensions as a second, third, etc. column.

With the above in mind, we can get a bit fancier.  Tableau permits sending a parameter value to the connection -- great, now instead of creating a connection for each dimension that I might want to have a Total presented in the above manner, I can instead use a comparison statement to the parameter to determine whether that field value will be 'Total' or what is on the row.  For most SQL, this comparison would be done using a Case statement -- nothing too difficult about that.  But, Superstore Sales is an Excel sheet -- and Excel doesn't permit Case statements in Custom SQL.  Instead of a Case statement, a Switch statement is used in the SQL.  First steps first, however, create the Parameter and fill the list with the names of the Dimensions that you may want to show as a total.  (BTW, if you pick a field that normally would be shown as a date, it will instead be treated as a text field -- you'll have to create a calculated field to convert the text field to a date field should you want to use Tableau's built in date capabilities).

So, after creating the Parameter (in my case it was named 'Total Field Chooser'), the above SQL will look like this <>:

Select dimension1, dimension2, dimension3, measure1 from Source
Union
Select
Switch(<Parameters.Total Field Chooser> = 'dimension1', 'Total', true, [Source].[dimension1]) AS [dimension1],
Switch(<Parameters.Total Field Chooser> = 'dimension2', 'Total', true, [Source].[dimension2]) AS [dimension2],
Switch(<Parameters.Total Field Chooser> = 'dimension3', 'Total', true, [Source].[dimension3]) AS [dimension3],
measure1 from Source

So, with the above in place as Custom SQL, I can modify the parameter and get the selected dimension filled with the word 'Total'.  One more task - create a calculated field that interprets the Parameter to select the Dimension -- for this, a simple Case statement in Tableau works fine. I named the calculated field 'Selected Total Field'

CASE [Total Field Chooser]
WHEN 'dimension1' then [dimension1]
WHEN 'dimension2' then [dimension2]
WHEN 'dimension3' then [dimension3]
END

Download the workbook (version 9.3) to see how this is built - it has three tabs, one connecting to Superstore Sales to show what Tableau's layout options are out of the box.  There is another with a 'fixed' column (Segment) that then has Total added.  Finally, there is a Custom SQL with Parameter driven version that permits a person to choose which Field is to have the Totals.  Enjoy.

Tableau Version 9.3 download

Copyright© Brad Earle
Comments are closed on this post.
Site Map | Printable View | © 2008 - 2017 KB Earle Associates LLC | |