Waterfall Chart with Dynamic Reference Band

12 min read

 

This blog aims to demonstrate how to build an effective Waterfall Chart. Firstly, we will demystify the waterfall chart: explain what it is and when is it useful. Next, we will demo how to build a waterfall chart in Tableau, and how to enhance its effectiveness and readability by creating dynamic reference bands.

 

Content

1. What is a Waterfall Chart
2. When to use a Waterfall Chart
3. Building a Waterfall Chart with Dynamic Reference Band

 

 

1. What is a Waterfall Chart

The Waterfall Chart, also known as Cascade Chart or Bridge Chart, is a type of bar chart that makes it easy to see how different components contribute to the net change between two points.

For example, the waterfall chart can visually illustrate how a beginning bank balance becomes the end balance through a series of additions (e.g. deposits and interests earned) and subtractions (e.g. payments and withdrawals).

The additions and subtractions can either be time-based (follows a chronological order), or category-based (often in descending order of importance or value).

 

 

2. When to Use a Waterfall Chart

The waterfall chart is often used in human resources, to visualize patterns in workforce growth and attrition. It is also widely used in financial analysis, to illustrate financial gains and losses over time. It can also easily keep track of the sales or profits contributed by each product line.

A major advantage of the waterfall chart is that it can reveal the interesting details that are often hidden in aggregated values.

For example, assume a company posted an annual profit of $100 million last year, and $100 million this year. On a line chart, it will be a flat line between the two years, and it may seem like nothing has happened. But if we can visually dissect and show each component that contributes to the aggregate profit, then we can reveal that in fact, core product lines suffered a $60 million loss and the company sold an office building at a $60 million profit. We can see, compared to a line chart, the waterfall chart tells us a much more alarming story.

 

 

3. Building a Waterfall Chart with Dynamic Reference Band

The Goal: 

We want to create a visualization that shows the contribution of each product subcategory to the total profit, and we want to make it easier for the user to read and interpret by enhancing the waterfall chart with dynamic colouring and dynamic reference bands.

 

The Data: 

We will demonstrate how to build an effective waterfall chart using the Sample – Superstore data set that comes with Tableau (Ver 2022.1 – en_GB-EU).

 

The Process:

Step 1: Plot a Bar Chart of Profit vs. Sub-Category

1. Place the Sub-Category dimension in the Columns shelf.
2. Place the Profit measure in the Rows shelf, use the default Sum aggregation.

 

 

Step 2: Convert to a Running Totals Gantt Bar Chart

1. Click on the Profit pill in the Rows shelf, select Running Total from Quick Table Calculations. This gives us the cumulative profit of the product sub-categories.
2. Change the Marks type from Automatic to Gantt Bar. 

 

 

 

Step 3: Adjusting the Gantt Bar Chart

1. Place the Profit measure onto the Size Marks Card, because we want the size of the Gantt bars to reflect each individual sub-category’s contribution to total profit.
2. Double click the Profit pill that’s on the Size Marks Card, and type in a “–” minus sign in the front to turn these values into negative. We are doing this because by default, positive profits go up in direction relative to the Gantt bars, but in the waterfall chart, we need each bar to pick up where the previous bar left off. Therefore, instead of going up, we need the bars to go down, so that the beginning point of each bar meets with the end point of the previous bar.

 

 

Step 4: Sorting the Gantt Bar Chart

1. Right click the Sub-Category pill in the Columns shelf, and sort in descending order by the Profit field. After sorting, the most to the least profitable sub-categories will be arranged from left to right.

 

 

Step 5: Creating a Parameter for Sub-Category

1. The easiest way to create this parameter is to just right click the Sub-Category dimension and select Create -> Parameter. This method will automatically populate the parameter’s list of values with values from the Sub-Category dimension, saving us manually typing out the text.  Remember to give the parameter a meaningful name.
2. Manually type in a “Total” in the list of values, as we will need this value for future use.

 

 

Step 6: Dynamic Label

Instead of showing the profit of all sub-categories at once, we only want to display the corresponding profit of the selected sub-category. This functionality will improve readability and help the users better understand which sub-category they are looking at. 

1. Create a new Calculated Field, give it a meaningful name, such as “Dynamic Subcat Label”.
2. Type in IIF([Sub-Category] = [Sub-Category Parameter], [Profit], Null). This expression means that if the sub-category is equal to the selected sub-category, then we return the profit, otherwise return nothing.
3. Drag this newly created calculated field onto the Label Marks Card.

 

We can further improve the user experience by make the colour dynamic based on selected sub-category.

4. Create a new Calculated Field, and give it a meaningful name.

5. Type in [Sub-Category] = [Sub-Category Parameter], this will create a Boolean, if the sub-categories match, it will return True, otherwise False.

6. Drag this newly created calculated field onto the Colour Marks Card.

 

But notice how our table calculation is now broken, because we have added this additional Boolean dimension into the view. To fix this, we have to edit our table calculation to include this new dimension.

7. Right click Profit pill on the Rows Shelf, and select Edit Table Calculation.

8. In the Compute Using box, select Specific Dimensions and make sure both dimensions in there are ticked.

The finished Waterfall Chart will look similar to the below.

 

Step 7: Creating the “Buttons” for the User to Select a Sub-Category

There are many ways to create such “Buttons”, here we illustrate a quick and simple approach.

1. Place the Sub-Category dimension onto Columns, and populate it with empty (“”) Text values.
2. Similarly, in a new sheet, type in “Total” as a string on the Columns shelf, and populate it with empty (“”) Text values.
3. You can reformat these by adding background colours or changing text fonts as you see fit.

 

 

Step 8: Creating the Total Profits Bar

Usually, the easy way to create a Row Total is to just use the “Totals” method in the Analytics Pane. But in this application, we want the total profits bar to display the selected sub-category’s profit as a percentage of total. However, in the main waterfall chart, the label displays the profit as currency value. We cannot use two separate number formats in a single chart, therefore, we need to create total profits as a separate bar chart.

1. Duplicate the waterfall chart sheet.
2. Drag the Sub-Category pill from Columns onto the Detail Mark Card.
3. Resort the Sub-Category to be consistent with the waterfall chart (shown below).

 

Now we can create a calculated field that calculates each sub-category’s profit as a percentage of total profit. And we want to make the label dynamic so that only the profit of the selected sub-category is displayed.

4. Create a new Calculated Field, and type in the expression as below.

5. Add the newly created Calculated Field onto the Label Marks Card.

 

Here is how the dashboard currently looks with our Waterfall Chart and Totals Bar Chart at the bottom and the Buttons on the top.

 

 

Step 9: Creating the Upper and Lower Bounds of the Dynamic Reference Bands

To create a reference band, we need to first establish the upper and lower bounds of the band.

The upper bound of each sub-category equals its Gantts bar’s value, which is the running sum of profit.

1. Create a new Calculated Field and type in the following expression.

The lower bound of each sub-category is calculated as the upper bound minus the size of the profit, which is equal to the running sum of profit – profit.

2. Create a new Calculated Field like before, and type in the following expression.

 

 

Step 10: Creating the Dynamic Reference Band

1. Drag the newly created upper bound calculated field onto the waterfall chart’s Detail Marks Card.
2. Go to the Analytics Pane, and drag Reference Line onto the Canvas, and into the Table option.

 

3. Right click the upper bound calculated field that’s on the Detail Marks Card, and select Edit Table Calculation. Select Specific Dimensions, and make sure both dimensions are ticked.

4. Repeats 1-3 for the lower bound calculated field.

 

 

Step 11: Create Parameter Actions that Link the Buttons to the Charts

1. Select Actions from the Dashboard menu.
2. Select Change Parameter from the Add Action menu.

3. Select your corresponding Source Sheet (e.g. Dashboard 1 in this case, but do give it a more meaningful name). Select the Target Parameter to be the Sub-Category Parameter that we had created, and Source Field to be Sub-Category.

4. Repeat 1-3 for the Total Button.

 

 

Final Viz!

We now have our waterfall chart of profit by sub-category. The chart features dynamic colouring, labelling and reference bands that correspond with the user’s selected sub-category. These features improve chart readability and the user’s overall experience!