Sign in or start a free trial to avail of this feature.
5. Waterfall Charts in Tableau
Waterfall charts are quite difficult to create in Tableau, particularly if you want to color-code each bar. In this lesson, we will learn how to create a waterfall chart in Tableau from scratch.
- Very effective chart for understanding how changes in annual metrics occur
- To create these charts, follow these steps:
--- 1: Create a column chart with designated order of columns
--- 2: Change the calculation to Running Total
--- 3: Flip the mark to Gantt
--- 4: Size the marks on the chart based on - Revenue
--- 5: To color-code the waterfall, it's best to use a Calculated Field
In this lesson we're going to learn how to create a waterfall chart in Tableau. A waterfall chart is normally used to explain how different components of profit or revenue contribute to a change in value between two consecutive time periods. In this example we're going to have annual recurring revenue for 2015 linked to annual recurring revenue in 2016. And we will have four components that will contribute to this change, new customer revenue, upsell revenue, churn and downsell.
Let's begin with a blank sheet in Tableau. The data in this example is very straightforward. In 2015 we had recurring revenue of 20.3 million Euro.
We had churn of 4.5 million Euro which means customers who left the service meant a reduction in revenue of 4.5 million. New customers contributed 11.5 million in recurring revenue and we got an additional 2.4 million from existing customers who upgraded their accounts. Existing customers who downgraded their accounts meant we lost 2.1 million from downsell.
Let's now create a sheet and build our waterfall. I'll start by taking our revenue component and putting it in columns and then I'll take the revenue figure and put it into rows.
The first thing we must do is fix the order of the revenue components and to do this I'll go to Sort, Manual and move these fields around into the correct order. I'll put churn last, I'll put new customers second and upsell second and this way I'll have two positive additions and then two negatives and then press OK and now I can move on with my analysis. In this particular example, we can't use bars as the mark. Instead we need to use a Gantt bar to create the waterfall technique and we also don't rely on a simple sum, we rely on a running total to take into account the previous values. This chart is now almost ready, however, we must add a measure to size for our Gantt bars and this is a little trick needed for waterfalls. The measure must be a calculated measure which is equal to minus recurring revenue. So, I'll create a new calculated field called Revenue, negative and simply write minus recurring revenue and press OK.
I'll now add this to size and as you can see, my waterfall is pretty much built. To give myself some space, I'll ensure fit width is selected.
While my waterfall is now working, I would like to add another column for 2016 ARR as well and to do this I'm going to go back into my Excel sheet which feeds the data into my workbook. In Excel I need to create a new row called 2016 ARR and add all of these components together to give me the correct number. However, for my waterfall to work, this needs to have a negative sign. So, I'll simply add all these values with the negative sign. I'll now save and return to my Tableau workbook. After refreshing my extract in Tableau I can see that the 2016 ARR column has now been included and my waterfall is pretty much complete. The last thing I'd like to do is to add a color code and labels to my chart and to do this I'm going to need to create a new calculated field and I'll call this Color-code.
And in here I'll create an IF function that will check if recurring revenue is greater than zero or if the revenue component equal to 2016 ARR.
Because this is actually a negative number but I'd like it the same color as the positive bars. If either of those are true, then I'd like to return the absolute value of recurring revenue; otherwise I'll simply return recurring revenue and then I'll type END to finish this particular calculation. When I now add this to color, I can see that a two step would work best here, so double clicking, I'll go with the two step and make the twp steps green and red, so I'll change this to green and press OK.
And now I have my waterfall color coded correctly.
I can also add color code to the label so that the labels are working correctly as well and as you can see, we've a nice waterfall showing each component of annual recurring revenue.