Creating Custom Waterfall Charts in BI Office
Ian Macdonald , Principal Technologist, Pyramid Analytics
A waterfall chart is a special type of column chart which is normally used to demonstrate how the starting position either increases or decreases through a series of changes.
The first and the last columns in a typical waterfall chart represent total values. The intermediate columns appear to float and show positive or negative change from one period to another, ending up in the final total value. As a rule, these columns are color-coded for distinguishing positive and negative values.
A waterfall chart is also known as a bridge chart since the floating columns make a so-called bridge connecting the endpoints.
These charts are quite useful for analytical purposes. If you need to evaluate a company’s profit or product earnings, make an inventory or sales analysis or just show how the number of your Facebook friends changed during that year, a waterfall chart is just what you need.
BI Office today has an out of the box method to create waterfall charts from that Chart selection.
BI Office waterfall charts.
However, currently there is no way to control the naming of the data series so that non English speakers can understand them (Endpoints, Finish, Positive, Negative). This can be an issue for non-English customers. You can easily create your own version by carefully organizing your data and using a standard BI Office Stacked Column chart type that is specially formatted. The blog details below address this scenario.
Building Your Waterfall Chart
Let’s start with our base data. Using the Pyramid Analytics Demo database, we’re going to create a waterfall chart for Sales from January 2009 to December 2009. Using our usual selection methods, we can easily create a grid containing Months in the Rows and Measures in the Columns:
Months in Rows and Measures in Columns.
A waterfall chart shows the rise and fall in values across the months, so we need to create a calculation that takes the current month and subtracts the previous month’s sales values to show the difference. We can use the Advanced Calculation Designer to create the new Measure, as it provides functional selections like Previous and Next Member.
In our calculation, we are using the Sales Measure and subtracting the value of the Previous Member of the Month Dates hierarchy from the current month:
Select Previous Member.
Let’s add our new Measure, Delta, to our Grid to see the Sales movement across the months (we’ll remove it later as we will be using other calculated measures to create our waterfall chart).
Add Delta to the grid.
Waterfall charts often color the positive and negative values, or the rise and fall, as different colors, typically green for rise and red for fall. We need to create two additional calculated measures, one containing the positive values and one containing the negative values, so that we can plot them and color them individually.
We can again use our Calculation Designer, this time using the MDX function IFf (very similar to the IFF function in Excel) to examine the value of Delta and assign the positive values to Rise and the negative values to Fall. Click on the “Other FX” button, expand the Logical folder and double click on the IIf option to place the syntax in the formulation window:
Creating the IFf function.
We then need to replace the items in carats (<>) with the test on our calculated Measure, Delta, to see if they are positive or negative; if positive, we set the value to a new Measure rise, or if negative to a new Measure, Fall:
Replace items in carats with Delta.
To create Fall, duplicate Rise, then change the “>” to “<=”, and multiply the value of Delta assigned to Fall by -1 to create a positive value to chart.
Note that if the test fails, we are assigning a null value to the Measure, i.e. it will show as blank in the cell of our grid and will not appear in our chart for that month.
Add Rise and Fall to our grid:
Add Rise and Fall to grid.
We now have our Rise and Fall measures, but in order for these to “float” or create the bridge between the starting and ending periods, we need to “prop them up” using a base measure. This is the Base from the previous month, plus the Rise from the previous month and minus the Fall for the current month. Again, the Calculation Designer lets us choose the measures and periods to correctly create this calculation.
We want to take the Base measure value from the previous time period (leaving the Measure selection blank will default to the Current Member, i.e. Base):
Then add the Rise from the previous Time Period:
Then subtract the Fall from the current Time Period:
Let’s add the new Base Measure to our Grid. We can also remove the Sales and Delta columns as we do not need them for our chart:
Add Base to grid and remove Sales and Delta.
That takes care of our columns of data. We now have our data calculated and arranged as needed in order to create the chart. However, it is usual in Waterfall charts to show the starting and ending position over the time period concerned. We can easily add Start and End rows to our data. The Start row will contain the value of the measure under consideration (in this case Sales) at the beginning of the first month, i.e. it will be the value of the previous month, whilst the End row will be the value at the end of the last month. In our case we need to set Start to be the value of Sales in December 2008 and set End to the value of Sales for December 2009.
Using Calculation Designer, this is very easy to do, but note that these calculated members are set to be a part of the Month Dates hierarchy, so that they will appear on the Rows:
Note details of Calculated Members.
Note details of Calculated Members.
We can now place these items into the Rows. You will need to use the “Custom Order” option for Rows in the menu ribbon so that the correct order is shown. Select Row Custom Order, then using Ctrl-Click, select Start, Months and End in the order you want them to appear. Note that we’ve also set the Grid type to Multi Flat:
Custom Order the items.
Now we can turn our attention to formatting the chart. As we’ve been so careful in setting up our data, this is a very easy thing to do.
Currently the chart is the default Bar Chart:
Currently Bar Chart.
Change the Chart type to Stacked Columns:
Change to Stacked Columns.
Stacked Column chart.
Finally, right click on the Series in the legend to change the colors of the data series, Rise to be Green, Fall to be Red and Base to be Transparent:
If you find that Base is above Rise or Fall in the stacked columns, you can reorder the chart either by reordering the columns in the grid or by using the “Change Chart Order” option from the right click menu against the legend item:
Reorder the Chart.
So that’s it! We have our waterfall chart.
You can continue to tweak the formatting of the chart to get it exactly how you want it to appear using the extensive chart formatting options available in BI Office.
You can, of course, add other analytic features like filers/slicers to view different aspects of the data set by different dimensions.
Finished Waterfall chart.
The calculations we built are hard-coded, restricting the chart to just showing Sales, as are the months we selected to appear in the grid, along with the Start and End periods. It would be great if we could make this chart more flexible by allowing us to select the start and end periods and the measure we want to visualize and have those drive the chart contents.
This can be achieved using BI Office parameters and the next article will examine how we can parameterize our waterfall chart to allow selection of the time periods and measure.
Until next time!
Acknowledgements: Thanks to my colleague Michael Raam for suggesting the initial approach to Waterfall Charts in BI Office.