For those doing any work in Finance related fields, requests for Waterfall charts (also referred to as “walks”, ex. Revenue Walk) in Tableau are very common. They can be a great way to understand how to get from one monthly total to the next, or how you arrive at a total over a period of time. In this instance, we are going to build a waterfall chart showing the revenues generated from the University of Cincinnati athletic program in 2017.

Step 1. Create Data Source for Blending

Step 2. Write Calc Assigning Steps

In this example my data is structured where each row represents one school, one year, and contains all the measures breaking down the athletic department revenues.

The first thing I do is to create a calc assigning a measure value to each of the field values that I am going to use as my waterfall columns. We will refer to this calc as [Revenues] for this example. Do this within the Waterfall.xls data source part of Tableau, not the base data source side.

CAse MAX([Field])
WHEN 1 THEN SUM([Data (NCAA Profit and Losses Combined)].[Ticket Sales])
WHEN 2 THEN SUM([Data (NCAA Profit and Losses Combined)].[Corporate Sponsorship, Advertising, Licensing])
WHEN 3 THEN SUM([Data (NCAA Profit and Losses Combined)].[Donor Contributions])
WHEN 4 THEN SUM([Data (NCAA Profit and Losses Combined)].[NCAA/Conference Distributions, Media Rights, and Post-Season Football])
WHEN 5 THEN SUM([Data (NCAA Profit and Losses Combined)].[Institutional/Government Support])
WHEN 6 THEN SUM([Data (NCAA Profit and Losses Combined)].[Student Fees])
WHEN 7 THEN SUM([Data (NCAA Profit and Losses Combined)].[Competition Guarantees – Paid])
WHEN 8 THEN SUM([Data (NCAA Profit and Losses Combined)].[Other Revenue])
WHEN 9 THEN -SUM([Data (NCAA Profit and Losses Combined)].[Total Revenues])
END

As seen above, each field value is defined by a value as defined by the value assigned in the THEN statement. In order to get all of the previous fields to show as adding up to the [Total Revenues] field in the Waterfall we make the last field negative.

As you will see, the running total Gant Chart will size each of the previous fields by their Sum(Value), but in order to get the final bar to go back to zero, you need to size it by the running total sum that the previous column amounted to. In this case my fields 1-8 all sum up to field 9, so when I go the inverse of field 9 the gant chart will go back to zero.

Step 3. Build the Chart

1. Drag [Field] to column
2. Drag your calculated fields showing the [Revenues] to rows
3. Quick Calc in [Revenues], Running Total across
4. Filter out the [Field] values that are not being used in the analysis
5. Change marks card to Gant chart
6. Drag [Revenues] to size in marks card.
7. Double click green pill for [Revenues] on marks card and make the value negative (add ‘-‘ before field name)
8. Add labels and color as necessary. In order to get the last column label to show as positive just duplicate your [Revenues] field as [Revenues Label] and make all values positive. Drop this to Text in marks card

One of the shortcomings of this methodology is that it is hard to create a waterfall chart with more than one total (numerous subtotals) due to the running total sum used to create the chart. If you run into an instance where you need subtotals, you can combine sheets and make numerous [Revenues] fields that all add up to your subtotal. Then have your first field equal the previous ending total, but just hide your field 1 so that the running total starts at your previous subtotal value.

Thanks so much for reading! If you have any more questions or comments about this blog post please feel free to reach out to me at spencer.baucke@gmail.com or DM me at @jsbaucke.