6 Replies Latest reply on Nov 17, 2016 7:03 AM by Jeremy Blaney

Modify Running SUM Calc - Add Label to Bottom of Waterfall

Hi everyone,

I need to add a label to the bottom of each bar within a Waterfall. I found a way to do this, but I can't figure out how to modify the running sum calc so this solution will work for the entire viz.

Here's where I'm at:

1. I created a dual axis chart. Both axis will be the same - I'm doing this so I can put a specific value at the bottom of each bar.
2. The brown bars (subtotals) don't need a value in the bottom chart

Left Side - Works great

• 2281 - 663 = 1618 (shows up exactly where it should)
• 1618 - 761 = 857 (shows up exactly where it should)
• 857 - 150 = 707 (shows up exactly where it should)
• 707 - 5 = 702 (shows up exactly where it should)

Right Side - Needs fixin'

• The calc is working as it should, however, I need to go a level deeper and subtract the individual value from the running sum to achieve my objective
• 702 + 8 = 710 (running total, where it's at currently)
• 710 - 8 = 702 (running total minus bar's value; it'll be at the bottom of bar)
• 710 + 27 = 737 (running total, where it's at currently)
• 737 - 27 = 710 (running total minus bar's value; it'll be at the bottom of bar)
• 737 + 349 = 1086 (running total, where it's at currently)
• 1068 - 349 = 719 (running total minus bar's value; it'll be at the bottom of bar)
• 1068 + 303 = 1389 (running total, where it's at currently)
• 1389 - 303 = 1086 (running total minus bar's value; it'll be at the bottom of bar)

I'm sure there's a way to do this by modifying the running sum calc and using conditionals to specify how a value for a certain Movement Type should be handled, I just can't figure it out. Any help would be much appreciated!

• 1. Re: Modify Running SUM Calc - Add Label to Bottom of Waterfall

Hey Jeremy,

What you are wanting to do here is place the total value of the previous bar as the bottom value of the current bar. To get the previous bar value, you just need to use the table calculation of lookup() with an offset of -1. The calculation on the bottom of the bar is: LOOKUP(RUNNING_SUM(SUM([Profit])),-1)

• 2. Re: Modify Running SUM Calc - Add Label to Bottom of Waterfall

I'm able to get the previous bar's values just fine, but I'm not able to put it at the bottom of the bar. Are you able to share your workbook so I can see what you did? Ultimately, I want to use that bottom value to represent % of total, excluding the first and last bars.

- Jeremy

• 3. Re: Modify Running SUM Calc - Add Label to Bottom of Waterfall

hey jeremy,

in this case what i did is a dual axis - both axis are the same (see the left and right legends - running sum of profit). on the left axis i set the mark labels to the top. and for the right axis i set the mark labels to the bottom.

• 4. Re: Modify Running SUM Calc - Add Label to Bottom of Waterfall

Hmm - I'm doing something wrong but I don't know what! When I pull in the previous value, it's not plotting correct. I uploaded a sample workbook, which mirrors my data set. Any help would be much appreciated!

• 5. Re: Modify Running SUM Calc - Add Label to Bottom of Waterfall

here you are jeremy. the workbook attached is in v10.1

• 6. Re: Modify Running SUM Calc - Add Label to Bottom of Waterfall

I have no idea why I couldn't do this on my own, but you sir have helped me immensely. Thanks!!!