For a standard waterfall, set the desired hierarchy and members on categories. The chart will then display each value as a waterfall accumulating in a total column/bar.
Right-click on a waterfall column/bar to access options:
- Total: set the category as a total, plotting from the category axis
- Subtotal: plots from the last total
- Custom subtotal count: if the category is set as a subtotal, you can set a custom count for how many previous categories it is the total for. If this is unset it subtotals from the previous total/subtotal (see example below)
- Negate: plot the categories in the negative direction
- Auto total after: add a total category after the selected one
- Auto total title: change the category label of the auto title. The default is "Total"
Members can be configured as totals/subtotals/expenses automatically using Business Rules.
Custom Subtotal Count
In the example below, Operating Expenses is set as a subtotal on the Business Rules sheet. Because 'Standard Cost of Sales' and 'Variances' aren't child members of 'Operating Expenses', and the previous total/subtotal is for Net Sales, the subtotal lines don't line up correctly. We instead want the line to connect from the Labour Expenses category.
When we use the right-click option to set the custom subtotal count, in this case set it to 10, then the subtotal is displayed correctly.
Variance waterfall charts display the categories contributing to the variance between totals. For example, in the chart below, the first half of 2018 had a value £103,246 greater than the first half of 2017, so this had a positive contribution to the change between FY 2017 and FY 2018.
The values for the total bars are set in the Categories section of the taskpane. The variance between each one is then split up by whatever is on the 'Split Categories by'. This can either be the same hierarchy, or a new hierarchy can be placed in this area.
In the example below, FY 2016, FY 2017, and FY 2018 are the selected members in the Date hierarchy on Categories. In the 'Split Categories by' section is the Organisation hierarchy, with the five divisions as the selected members. This results in waterfall chart showing how each division contributed to the variance over each year.