One of the most consistent challenges I often see with Tableau is creating very specific visualizations under constraints of the data. The most common of these are creating waterfall charts with multiple measures and wanting to apply filters across these visualizations.
In Tableau, the solution is not always straightforward. If you were creating a traditional waterfall chart, you would put a dimension on rows, add a measure on a column, and apply a table calculation for size.
Previously, there were other ways to create waterfall charts with multiple measures, but the filtering was limited–and to get around some of the formatting challenges you had to use a parameter.
Thanks to upgrades in Tableau that allow for logical joins–or as Tableau often refers to them, relationships–we can now build a waterfall chart with multiple measures and add a filter.
For this example, I am going to work with an online retail data source that Tableau provides with every copy of Tableau Desktop–Sample – Superstore. I’ve modified the data to include additional columns around where costs are associated, specifically:
- Manufacturing costs
- Transportation costs
- Overhead costs
- Promo costs
I’m going to use these four fields, plus the Sales field to create a multiple-measure waterfall chart with filters.
Here is the tutorial for how to create these charts.
Create a Supplemental Data Source
To make this chart type work, we are going to need an additional data source. Open up your text editor and create the following single-column csv.
point
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20
This will create a single-column data source with a column called a point that counts up from 1 to 20.
Save the data source as placeholder.csv and close your text editor.
Step 1: Connecting and Modeling the Data
First, I am going to connect to my primary data source, the modified Sample – Superstore dataset. After connecting to the data source, I am going to also connect to the placeholder.csv data source.
Add the Sample – Superstore data source to the data model, then add placeholder.csv. They will not have a relationship so you need to set that relationship. For both data sources, you will need to create a calculated join and set the values to 1.
Once your data sources are related you can create a new sheet.
Step 2: Specify the Dimensions
Create a new calculation called “Label.”
“Label” will be the labels at the bottom of the waterfall chart. We’ll use the Point column from our placeholder.csv data source to set these values. Your example might be a little different, but it should all be very similar regardless of the specifics waterfall you are trying to create.
// Label
CASE [Point]
WHEN 1 THEN "Manufacturing Costs"
WHEN 2 THEN "Transportation Costs"
WHEN 3 THEN "Overhead Costs"
WHEN 4 THEN "Promo Costs"
WHEN 5 THEN "Total Costs"
WHEN 6 THEN "Profit"
WHEN 7 THEN "Total Revenue"
END
You will also notice that our waterfall contains 7 values, but our placeholder.csv data source has 20 values. I personally like to have more values in my placeholder.csv file than what the chart will contain because sometimes stakeholders want more values later on, and I want to make it easier to add values later.
Step 3: Add Label to Columns
You’ll notice these values are not ordered correctly. Right-click on the [Label] dimension on Columns and add a custom sort using a calculated field. Sort ascending by the minimum of [Point]. Exclude the Null values from the visualization–these are all the unassigned values of the [Point] column.
Step 4: Create the Starting Point of the Waterfall Chart
The next step is to figure out where each of your waterfall points should start. These typically start at zero and accumulate over each step. For my first value, it is set at zero, these values then roll up. In my examples, I am using negative values, this is because my data source includes negative values.
Create a calculated field called [Start]:
// Start
CASE [Point]
WHEN 1 THEN 0
WHEN 2 THEN -([Manufacturing cost])
WHEN 3 THEN -([Manufacturing cost] + [Overhead Costs])
WHEN 4 THEN -([Manufacturing cost] + [Overhead Costs] + [Transportation Costs])
WHEN 5 THEN 0
WHEN 6 THEN -([Manufacturing cost] + [Overhead Costs] + [Transportation Costs] + [Promo Costs])
WHEN 7 THEN 0
END
You’ll notice that in values 5 and 7, I have the values starting at 0. This is because I wanted some sub-totals in my waterfall. The great part about this technique is that you can add sub-totals wherever you want.
Add [Start] to columns and change the mark type on the marks card to Gantt.
Step 5: Make the Waterfall
The next step is to add value to the size on the marks card. Create a new calculation called [Value] that we’ll use to specify the heights of each bar and to add labels.
// Value
CASE [Point]
WHEN 1 THEN -[Manufacturing cost]
WHEN 2 THEN -[Transportation Costs]
WHEN 3 THEN -[Overhead Costs]
WHEN 4 THEn -[Promo Costs]
WHEN 5 THEN -([Manufacturing cost] + [Transportation Costs] + [Overhead Costs] + [Promo Costs])
WHEN 6 THEN [Sales] + ([Manufacturing cost] + [Transportation Costs] + [Overhead Costs] + [Promo Costs])
WHEN 7 THEN [Sales]
END
Place [Value] on size on the marks card, as well as label. Format the label to your specifications.
Step 6: Add Color for Context
Adding color for context can be really important, especially when separating the totals from the subtotals. To do so, I’ve built the following calculation:
// Color
IF MIN([Point]) IN (5, 7)
THEN "Total: "
ELSE "Sub: "
END +
STR(SIGN(SUM([Value])))
You’ll notice that I have an IF statement that explicitly calls out values 5 and 7 in the point Column. This will give us a prefix of “Total: “ for those values–and allow me to color these values differently from the sub-totals which I have called “Sub: “ in the IF statement.
Finally, I use the SIGN() function to show the direction of the change in the waterfall chart. If a value is positive, SIGN() returns a 1, if it is negative, it returns a -1, and if it is 0, it returns 0. I add STR() around the SIGN() function to turn it into a string and combine it with my label in the first part of the calculation.
From there, add it to color, and set the colors.
Step 7: Add Guidelines
There are several ways to do the next step, but here is what I like to do. First, create an adhoc calculation by double-clicking right of SUM([Start]) on the rows shelf. Then type SUM([Start]) + SUM([Value]) and hit enter. This will create a second waterfall chart and add a second marks card.
On the new marks cars for SUM([Start]) + SUM([Value]) remove values for color, size, and label. Change the mark type to line. This will now display Path as a mark option. Change the Path to Step. Set the size to very small and the color to dark gray.
Now swap the order of SUM([Start]) and SUM([Start]) + SUM([Value]) on the rows shelf.
Step 8: Format your Chart
Finally, create a synchronized dual-axis between the two charts.
Remove axes, Label header, and excess gridlines.
Step 9: Add Filters
Now add the filters of your choice. Here I will add [Region] as a filter. I’ll exclude the South for this example and you will see a working waterfall chart that uses multiple measure and applies filters.
Summary
By using Tableau’s logical relationships, we were able to overcome some significant challenges of our data to build a multiple measure waterfall chart with filters
Do you have more questions about Tableau? Talk to our expert consultants today and have all your questions answered!
