Measures Waterfall

Owned by Steve Martin
Last updated: 06 Mar, 2019


How to: Measures Waterfall (09:28) 37.4MB

The waterfall of measures - a chart designed to measure the cumulative performance of different measures working towards a grand total; much like a normal waterfall however, a normal waterfall tends to plot a single measure - 'Sales' or 'Profit' etc against a group of dimensions similar in function to a funnel chart, instead of working from largest to smallest, this plots as expected - a waterfall.

This is not theoretically possible in that it breaks the rules of Tableau, that of needing a workable measure with which to plot; the fundamental function behind Tableau; remove the dimensions, and Tableau ceases to show a measurable output, such that in order to get this to work, we need to present partitionable, and this, measurable data that cannot be linked to the data in the source, lest the source data - the data to be measured, will become tightly connected to the chart, such that it will need to be discounted from measurement, which will ultimately lead to incorrect reporting.

As a result, in order to build this chart, we must make a very slight alteration to the source data with the addition of a new column and a single nulled line, then we are good to go.

A neat little function of Tableau, present since at least 2011 but still largely unknown is the Show Missing Values function: Only available for continuous data that can be easily sequenced such as dates, this function has the illusion of creating data where data does not exist. It was primarily designed for date functions where it is necessary to illustrate no data for the given date, however, we can re-purpose this function to provide a usable output not linked to the main data

Instructions


  1. Modify your data by adding a new a new column something easily recognisable such as Waterfall
  2. Now insert a new null row. You will now use two easily identifiable markers here, any thing you like just ensure that the null line receives one mark, and the remaining fields receive the other:

    1. I tend to call the null line "start" and all remaining rows in the set as "end"

      SQL Example 
      -- Using Superstore Sales as an example

Select
Segment
,Category
,Order_Date
,Shipping_Mode
,Customer_ID
,Sum(Sales) As Sales
,Sum(Profit) As Profit

From orders

Group By
Select
Segment
,Category
,Order_Date
,Shipping_Mode
,Customer_ID

-- will become:

Select
Segment
,Category
,Order_Date
,Shipping_Mode
,Customer_ID
,Sum(Sales) As Sales
,Sum(Profit) As Profit
,'start' As Waterfall

From orders

Group By
Select
Segment
,Category
,Order_Date
,Shipping_Mode
,Customer_ID

Union All
Select
Null As Segment
,Null As Category
,Null As Order_Date
,Null As Shipping_Mode
,Null As Customer_ID
,0 As Sales
,0 As Profit
,'end' As Waterfall

From orders
  3. With the data prepped, its now time to load it:

  4. Once loaded, create a new calculation "waterfall_build" - and define the extra number of days as being one less than required, so if using Sales, Profit, Quantity and Forecast and then Grand Total, only define +4 rather than +5 as today will be counted as one field

    waterfall_build
    If waterfall = 'start' Then Today() Else Today() + 4
  5. Now, drag the [waterfall] field onto the rows, and right-mouse drag the waterfall_build value beside it as discrete dates, notice that Tableau has assigned the values as expected.
  6. Now for the 'magic':
    1. Right-mouse select the waterfall_build and select Show Missing Values - nothing will happen
    2. Now, remove the [waterfall] field from the view, notice that now, Tableau has plotted five fields: the start and end date, and the missing dates in between.
  7. Switch the chart so the waterfall_build is now on the columns

  8. Next, define your headers:

    We are using table-calculations, specifically, index to tell Tableau exactly what to plot where: Using the date-plot, Tableau will treat each field as a new index value allowing us to plot the chart

    headers
    Case Index()
	When 1 Then 'Sales'
	When 2 Then 'Profit'
	When 3 Then 'Quantity'
	When 4 Then 'Forecast'
	When 5 Then 'Grand Total'
End

    And then place onto your chart beside the waterfall_build field

    1. Right-mouse select the waterfall_build field and deselect Show Header so now we have successfully disguised the date fields with our own defined fields from headers
  9. Now the final set of calculations:

    1. Values to calculate the numbers:

      It is imperative to use the Total() function here, this will sum the complete value for your set and plot that one value, otherwise, Tableau will attempt to plot the value against a non-existent dimension

      Values
      Case Index()
	When 1 Then Total(Sum(Sales))
	When 2 Then Total(Sum(Profit))
	When 3 Then Total(Sum(Quantity))
	When 4 Then Total(Sum(Forecast))
End

    2. Cumulative, used for the output of the grand-total, as the normal grand-total will not work here as the fields are superficial:

      Cumulative
      Running_Sum([Values])

      we do not need to aggregate this value as the values are already aggregated in the previous calculation

    3. Treads to provide us with the drops, so these are negative as with a normal waterfall; we run the negative on the values for the measures, and a negative on the cumulative for the grand total:

      Treads
      If Index() < 5 Then -[Values] Else -[Cumulative] End

    4. Output Values - these are the actual values, so this field is nearly identical to the treads but for the removal of the sign:

      Output_Values
      If Index() < 5 Then [Values] Else [Cumulative] End
  10. And now, we just need to put the waterfall together:
    1. From the Marks card, switch the chart type to Gantt
    2. Drag Cumulative  to the rows - As a Gantt chart, Tableau will only plot the highest point of the data
    3. Drag Treads onto the size mark to build the drop
    4. Drag Output_Values onto the labels

So now all that remains is to format the chart to remove gridlines, and axis lines; the chart can easily be controlled by filters and/or action filters and, with some work, it is possible to add or remove (pre-defined) measures to adjust the chart at will.