Adding Custom Sub-Totals in Tableau

Mar 7, 2020

Klaus Schulte

Introduction

When it comes to visualizing financial data, it is often required to combine cumulative and non-cumulative values in one visualization (and yes: tables are also visualizations 🤓), like for example in a Profit & Loss statement.

While positions like revenues, cost of sales, selling expenses or income taxes are non-cumulative, we also have sub-totals like gross profit, operating profit (EBIT), profit before income taxes (EBT) and net profit, which are cumulative on sub-total level.

Unfortunately, we cannot combine both – non-cumulative positions and cumulative sub-totals – in the Tableau default.

If we would go for normal aggregations (typically ‘sum’ of something), then we would also have normal aggregations for the sub-totals.

If we would go for running sums, it would look like this:

I wrote about the special context of visualizing financial data before and I also know a lot of finance people personally. And from all what I know, these people will never ever accept neither the first nor the second solution.

A typical workaround would be to integrate the sub-totals in the data source. In this blog however, I would like to introduce four ways to solve the problem described and to combine normal aggregations for the positions and running sums for the sub-totals.

1 Custom Sub-Totals with Data Densification

The easiest and quickest way to solve this task is to densify the data and to bring in a second copy.

Based on these table names you can then define a dimension ‘copy’ which can be 1 or 2.

Next step is to define a new position…

…and the adjusted value:

And that’s it!

2 Custom Sub-Totals with Internal Data Densification

The first solution requires data densification. Sometimes you are not able to densify the data, for example when the data source is pulled from a Tableau Server and the data engineering guy is just not available (for you).

Internal Data Densification is a technique to densify the data within an existing data source. To apply Internal Data Densification, it needs granular data to be able to create (in this particular use case) two partitions on aggregated level.

In my sample data, I have P&L data on month level and would like to visualize the P&L on year level. To get the necessary two partitions or copies, I’m using the month dimension and the modulus function:

Based on this field, I’m again able to define my position_new and my value_new. Compared to the data densification solution, I have to use Fixed-LODs for the new value.

The result should be the same 🙂

3 Custom Sub-Totals with Table Calculations

When I teased this blog post on Twitter earlier this week, Zen Master Rody Zakovich came up with a third solution I have never thought of and which I think is just amazing.

Drawing on the example of Superstore Sales, Rody is using First() and Last() to create his Sales_adjusted.

However, for my P&L use case the solution with floating text boxes for the labels wouldn’t work when switching between cost-of-sales- and total-cost-method because of different row lengths.

4 Custom Sub-Totals with Table Calculations & Labels

Against this background, and based on Rody’s solution, I could come up with a solution for the label problem.

This solution is, however, um, quite hacky… 🙂

You can bring in a label on the text shelf which is calculated the same way like Rody’s Sales_adj:

With same blanks between this label and measure values you get something like this:

To align the labels uniformly right-aligned, in a second step I added digits and thousands separators to compensate for the different length of the sales values.

Yes, this is wild. And I’m sorry for this. But it works 🙂

EDIT:

After publishing the blog, Jeff Shaffer came up with a simpler and very elegant solution for the measure and for the label problem. Thanks Jeff! I recommend to use this approach instead of the solution descibed above.

Wrap Up

In this post, I described four more or less hacky solutions to combine normal aggregations and table calculations in one visualization. Massive thanks to Rody Zakovich and Jeff Shaffer for their solutions.

You can find all the solution in this workbook on Tableau Public:

I hope you enjoyed reading and that you find use cases for this. If you have questions, reach out to me in the comments below or on Twitter where you can find me under @ProfDrKSchulte.