Creating Pocket Price Waterfall Chart in Tableau

Tutorial: Pocket Price Waterfall Chart in Tableau

Pocket Price Waterfall Chart as the name suggests shows the transition from List Price to Pocket Price or in words that are more complex; depicts the leakage from List Price to Pocket Price. The consulting firm McKinsey popularized the Pocket Price Waterfall or simply the Price Waterfall chart.

Its main applicability is in understanding the transaction level pricing which helps firm identify opportunities to stop the unwanted leakages from List Price or Invoice Price. Essentially Pocket Price Waterfall shows how much revenue companies really keep from each of their transactions.

Pocket Price Waterfall can also be looked at Brand level or company level not necessarily used in analysing transaction level details only.

Now that we have briefly understood what Pocket Price Waterfall is, we will look at how to create Price Waterfall chart systematically with the help of Tableau. Unlike other charts, Pocket Price Waterfall is not readily available in Tableau. One has to do a series of steps to arrive at the desired results.

Data

Based on my experience working in pricing domain, I have seen Waterfall elements such as List Price, Invoice Price, Discount, Rebates and Pocket Price being modelled as a separate Waterfall dimension even though the elements themselves represent measures. The advantages are;

– It makes analysis simpler

– It also allows for easier implementation as it is a customized chart

– One can create a Waterfall hierarchy to create multiple level of details

I have created a dummy data for the purpose of this blog and table below summarizes the Dimensions & Measures within the data

The below table shows the dummy data which will be used for creating Price Waterfall chart. As one can see, we have two levels of details e.g. Distributor Discount is bifurcated into Standard Distributor Discount, Special Distributor Discount and End-Customer Discount at level two. Similarly, Element Type indicates that List Price, Invoice Price and Pocket Price will appear as whole bars whereas rest of the elements will float in the chart as shown in sample Price Waterfall above. The rule of thumb is to make all the Price Points as Whole type.

Data Exploration & Visualization

Again, we will create a series of steps so that it is easier to understand how the chart is constructed.

Step 1: Connect to data.

I have prepared the data in MS Excel format hence we will choose MS Excel as data source.

Step 2: Create a Waterfall hierarchy. Drag Waterfall Element Level 2 on Waterfall Element Level 1 and name the hierarchy as Waterfall Element.

Step 3: Place Waterfall Element on Column shelf.

Step 4: If one observes all the elements are sorted in ascending alphabetical order but that is not desired. We must start with List Price and end with Pocket Price. One needs to sort the Waterfall elements manually as shown below. Move the elements up or down as per desired order.

The sorted elements look as shown below.

Sort Waterfall Element Level 2 similar to steps above.

Step 5: Put Value on Row shelf. However, this is not Pocket Price Waterfall chart.

Step 6: The steps become slightly complex from here on. First, convert the Marks type from Automatic to Gantt Bar.

Step 7: Let us convert the chart into text table so that we understand the subsequent calculations that we are going to perform.

Step 8: Create a calculated field to reverse the sign of Element Type Whole as shown below.

Step 9: Now put the Reversed Value on text table alongside Value. We have just reversed the sign of the value for Whole Element Type.

Step 10: Create a calculated field “Difference from Previous” as shown below. This step deserves some explanation. For Whole Element Type we are not changing the value and for Float Element Type we are calculating the difference of value from previous element and current element. Note that Previous_Value() function returns the value of the current calculation from previous row. This is a seriously handy and useful function in this kind of scenario.

Step 11: Now put the Difference from Previous calculated field on text table.

Step 12: Now create a chart with Marks type as Gantt Bar for calculated field Difference from Previous on Row shelf and Waterfall Element hierarchy on Column shelf as below.

Step 13: We are not done yet. We will use Reversed Value calculated field to extend the size of the Gantt chart in upward direction for Float and downward direction for Whole Element Types. Drag and drop Reversed Value on Size card.

Step 14: That generates our Price Waterfall Chart. Now let us give some final touches in terms of formatting. We will use Reversed Value for colour attribute as shown below. The formatting and colour scheme is subjective in nature.

Step 15: Finally, we will use Value measure for Labels and rotate the axis labels so that they are visible.

Step 16: We have our drillable Price Waterfall Chart ready.

Stay tuned for more exciting visualizations and learning with Tableau.

About Tableau

Tableau (NYSE: DATA) (Title = Newyork stock exchange) headquartered in Seattle, Washington has a mission to help people see and understand data. It offers a product portfolio for data visualization focused on business intelligence.

One can visit the official Tableau website to find more details about Tableau and its product offering and features.