How to Create Revenue Waterfall Dashboard

Revenue Waterfall Dashboard:
Example Dashboard:

Shown above is an example implementation of Revenue Waterfall Dashboard. Although the waterfall here is shown comparing the Year 2016 to 2015, we can configure this to compare Quarters, Months, Weeks, Days or any other Time Period you can think of. The different components (from L to R) of that 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 but they 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 but they 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).
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 to create the dashboard:

1. Dropdown Selections: Use DatamartContext. dimFilterEntry("Name", Column) to get dropdowns in the dashboard.

1. Create tables for time periods in question using SelectQuery(..computed columns..).where(api.newDatamartSlice("DateColumnName", period)) for both the periods. In our example case, we obtain columns like Revenue, Quantity and PricePerUnit from these tables

1. Read revenues from Time Periods just by obtaining SUM(Revenue) from the tables created in step 2

1. 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.

1. 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.

1. Next, we fetch the business that doesn't fall in lost or new business category and we'll 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"

1. 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'll have the total Volume Effect.

1. 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.

1. 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.

1. 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 Dashboard shown here.