How to Create Revenue Waterfall Dashboard

The following example shows how to implement a Revenue Waterfall Dashboard. It compares the year 2016 to 2015 but we can configure it to compare Quarters, Months, Weeks, Days etc.

The different components (from left to right) include:

• Revenue in Time Period 1 – Total revenue in Time Period 1.
• Lost Business – Revenue from lost customers + Revenue from Retired Products + Revenue from current customers not buying products in Time Period 2 (who did buy the same products in Time Period 1). 
• New Business – Revenue from new customers + Revenue from New Products + Revenue from existing customers buying products in Time Period 2 (who didn't buy the same products in Time Period 1).
• Price Effect – Changes in total revenue due to changes in Price Per Unit of all products. 
• Volume Effect – Changes in total revenue due to changes in volume of goods sold to customers.
• Portfolio Mix Effect – Changes in total revenue due to change in mix of sales (changes in proportions of a product's sales to total sales for time periods in question). For example, over a given time period, increase in share of a high-priced product will contribute to revenue positively and vice versa.
• Other Effects – Anything not accounted for by any of the above-mentioned effects.
• Revenue in Time Period 2 – Total revenue in Time Period 2. 

Technical details of the dashboard creation:

1. Drop-down selections: Use DatamartContext.dimFilterEntry("Name", Column) to get the drop-downs in the dashboard.
2. Create tables for time periods in question using SelectQuery(..computed columns..).where(api.newDatamartSlice("DateColumnName", period)) for both periods. In our example, we obtain columns like Revenue, Quantity and PricePerUnit from these tables.
3. Read revenues from Time Periods just by obtaining SUM(Revenue) from the tables created in Step 2.
4. To get Lost Business, we use "SELECT T1.* FROM T1 LEFT OUTER JOIN T2 ON T1.Product=T2.Product AND T1.Customer=T2.Customer WHERE T2.Product IS NULL" where T1 is the comparison period and T2 is the current period (or the more recent of the two). Select Revenue from the result to get the Lost Business Amount.
5. To get New Business, we use "SELECT T2.* FROM T1 RIGHT OUTER JOIN T2 ON T1.Product=T2.Product AND T1.Customer=T2.Customer WHERE T1.Product IS NULL" where T1 is the comparison period and T2 is the current period (or the more recent of the two). Select Revenue from the result to get the New Business Amount.
6. Next, we fetch the business that does not fall in lost or new business category and we will call it common business. The easiest way to get that data would be to use the query "SELECT * FROM T1 INNER JOIN T2 ON T1.Product=T2.Product AND T1.Customer=T2.Customer".
7. To get the Volume Effect, we iterate through common business data and compute ((T2.Volume - T1.Volume) / T1.Volume) to get the fraction increase in Volume and multiply that by T1.Revenue to get the volume effect for that row of data in the common business. Add that up over all the rows and we will have the total Volume Effect.
8. To get Price Effect, iterate through common business data and compute (T2.PricePerUnit – T1.PricePerUnit) and multiply that by T2.Volume. That will give us the change in revenue due to change in Price Per Unit for each row of common business. Now add this up for all the rows to compute the total Price Effect.
9. To calculate the Portfolio Mix Effect, iterate through common business data and compute (T1.PricePerUnit) * (T2.Volume/SUM(T2.Volume) - T1.Volume/SUM(T1.Volume)) for every row. Multiplying the sum of these computed values to the SUM(T2.Volume) will give us the total Portfolio Mix Effect.
10. Plotting all of these computed items using a custom waterfall template will allow you to create a dashboard that will look similar to the example shown here.