Tableau Waterfall with Subtotals

My partners in crime, Ron Keler and Anil Bharadwa, convinced me to let loose and embrace the Avenger theme to showcase some advanced Tableau tricks and techniques. Ron and I originally created this content for a recent guest speaking event for a client and it turned out to be a lot of fun.

This is the second post in this series where we take each one of the Avenger superheroes and relate their character or power to some feature that can be used to save our data visualization. (Yes…we know it’s a stretch).

The first post Avengers Save Dashboards - Tableau Data Write Back focused on Iron Man and his superior engineering capabilities. In this article we hone in on Black Widow, Natasha Romanova, who is an expert spy, athlete, and assassin for Shield. One of Natasha's most visible strengths is her acrobatic hand to hand combat fighting skills. So, we applied these characteristics to saving our Tableau dashboard by adding some creative data and chart acrobatics to improve waterfall visualizations.

Waterfall charts are data visualizations that help in understanding the running total effect of sequentially introduced positive or negative values. Typically, waterfall charts are used in subject areas involving:

• Profitability (Revenue -> Fixed Cost -> Variable Cost -> Profit)

• Inventory (Units in Stock -> Damaged ->Refurbished -> Sellable Units)

• Sales Revenue and Pricing

Tableau does not have a built in waterfall chart but there are many postings on the topic that show the common technique of using a Gantt chart with a running total and applying the opposite sign of the measure to represent the bar size and direction. It works great. But adding subtotals is problematic because of the ‘running total effect’ in that the subtotals are aggregated in the overall running total. To illustrate this, first review the waterfall image below. Notice that there are no subtotals. This is a basic waterfall showing Iron Man’s total Income and his expenses.

But what if the requirement was to show subtotals like in the illustration below. This waterfall has subtotals for ‘Net income after tax’ and ‘Income after Avenger Cost’.

So how does it work?

First, we will step through creating the basic waterfall and then we will add the subtotals. The premise for this waterfall is that we are using row based data with a single waterfall measure.

Using this row base data we simply follow the below common approach to build a waterfall chart.

1. Create a running total on the waterfall measure (place on Rows)

2. Select Gantt Bar chart type

3. Create a calculated field that is the opposite value of the waterfall measure (e.g., -1*[Water Fall Measure])

4. Place the calculated ‘Opposite Waterfall measure’ on the Size mark

5. Place the actual Waterfall measure on the Color and Label mark

To add subtotals to the waterfall, we first need to manipulate the data source. We add the subtotals as new category types with a Waterfall amount equal to 0 or null. Next, we added a new measure column that is identical to the original waterfall amount column but we include the correct subtotal values. We will name this Waterfall Label.

Using this modified row base data we follow the below approach to build a waterfall chart with subtotals:

1. Create a running total on the ‘Waterfall Amount’ measure (place on Rows)

2. Select Gantt Bar chart type

3. Create a calculated field that is the opposite value of the ‘Waterfall Label’ measure

(e.g., -1*[Waterfall Label])

1. Place the calculated ‘Opposite Waterfall Label’ measure on the Size mark

2. Place the actual Waterfall Amount measure on the Color mark

3. Place the actual Waterfall Label measure on the Label mark​

So, applying a little creative data and chart acrobatics and leveraging Tableau’s visual strengths concepts with size, label, and color we were able to SAVE THE DAY and produce a waterfall chart with subtotals.

Enjoy