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 |
I tend to call the null line "start" and all remaining rows in the set as "end"
-- 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 |
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
If waterfall = 'start' Then Today() Else Today() + 4 |
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 |
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
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 |
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 |
Cumulative, used for the output of the grand-total, as the normal grand-total will not work here as the fields are superficial:
Running_Sum([Values]) |
we do not need to aggregate this value as the values are already aggregated in the previous calculation
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:
If Index() < 5 Then -[Values] Else -[Cumulative] End |
Output Values - these are the actual values, so this field is nearly identical to the treads but for the removal of the sign:
If Index() < 5 Then [Values] Else [Cumulative] End |
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.
The content by label feature displays related articles automatically, based on labels you choose. To edit options for this feature, select the placeholder below and tap the pencil icon.
|