Waterfall charts with multiple totals

andrewdempsey · 2024-12-23T01:05:12+00:00

There was an error rendering this rich post.

Hi,

I am wanting to create a waterfall chart with multiple line items across financial years. This is also dimensioned by forecast version, where the closing balance of a given year depends on the version, as well as the forecast range. I have created a dynamic hierarchical list as per the below screenshot to get around this. This works well, but ideally I want to exclude the 'starting balance' items and have the total reset at each closing balance (as per the first year).

Anaplan is aware that these are 'intermediate totals', but these do not reset the sum to 0.

Thanks!

waterfall chart.PNG

Accepted answers

All comments

seymatas1

Hi Andrew,

I recommend creating a dedicated module for the waterfall chart. You can remove the unwanted line items or reference the line items from the original module to avoid the "auto-detected" totals.

I hope this helps.

Seyma 🌷🙂

TristanS

@andrewdempsey If my understanding is correct, you want your waterfall chart to look like example below? If yes, you would have create a "fake" waterfall chart using a stacked column chart like the one below. In the example below, in theory you can get rid of the FY22 Opening Volume and Opening Volumes for future years as the assumption would be the opening balance of one year is the closing volume of the previous year.

Below is how I built it

Create a module having the financial metrics as line items and the list dimension "Waterfaller" (screenshot 1)
Create a list "Waterfaller" with the following (screenshot 2)

Screenshot 1:

The formula section "can" have calculations in it but my intention is to just show how you can fake a waterfall using a stacked column so will leave it to you how to either auto calculate or populate using actions.

Screenshot 2

List has 3 components:

FY Closing - not really needed but helps with color coding the end of the waterfall
Components - individual components you had in your waterfall
. - this is used to create the fake waterfall effect. Intentionally named the lineitem a period (.) so that users will barely see it in the chart

Below is the data I entered

This is how it is configured in the UX card

For cancellations, if you want it to appear as red then add another list item "FY Cancellations" to the Waterfaller list, set the data up accordingly and then in the chart options have it display as red

TristanS

@andrewdempsey lol just realised you can achieve what you want and still use a waterfall chart if you are willing to remove the "Opening Volume" item starting from FY26. To be clear, keep FY25 Opening Volume and just remove Opening Volumes for future years. That should give you the chart you want, that is if removing the opening volume for future years is acceptable. You can rename the "FY25 Closing Volume" to "FY25 Closing Volume / FY26 Opening Volume" and so forth

andrewdempsey

@TristanS thanks this is exactly what I am after! The column graph looks good as a backup solution, but will try to retain the waterfall if I can as it's a bit cleaner. I did attempt the above prior, but because the closing balances are in the parent list of the others, the sums don't work as intended. Could you please provide an example if you have managed to get it working?

TristanS

@andrewdempsey

So as not to touch the original line item formulas as I am not familiar why it was done that way. E.g. the closing balances for FY26 onwards seems to have been calculated as negative. Gut feeling was that it was only done for the waterfall chart.

Introduce new closing balance line items below with the following formulas

FY26 Closing Balance PoC = FY25 Closing Balance + FY26 Sales + Cancellations FY26 + Net Others FY26 (note that closing balance line item uses previous years while all other line items in the formula uses the current year)
FY27 Closing Balance PoC = FY26 Closing Balance + FY27 Sales + Cancellations FY27 + Net Others FY27 (note that closing balance line item uses previous years while all other line items in the formula uses the current year)
FY28 Closing Balance PoC = Use same patterns as above
FY29 Closing Balance PoC = Use same patterns as above

2. In the waterfall chart

Hide the original FY Closing balance line items
Hide the original FY Opening Volume line items (starting from FY26 .. you need to keep the FY25 Opening Volume)
Include the new Closing Balance PoC line items

You should get a result similar to below

Quick Links

Can you help?

Unanswered questions

Hide the cards under "Models" section once connected
Hi Is there a way to hide the cards under the "Models" section once connected, as I would like the users to see only the links to the apps? Thank you
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…