After writing my first blog post on waterfall charts, I received an email from Filip Pecserke answering a question I had posed about subtotals. I asked him if I could write up his solution in a blog post, so thanks Filip! Here is a solution for adding subtotals into your waterfall charts using the same data set and blend method in my previous post.

The method I showed in my first post utilized a Gant chart and a running sum table calc to get the bars to build off of one another. With this method, it was difficult to add in subtotals because the table calc would see those subtotal values as just another field and would add those onto the previous value. The workout is to add in a field that indicates where a field is a regular value or a subtotal. This way you can exclude these subtotals from your running sum table calc.

IF [Field]=9 or [Field]=20 or [Field]=21 THEN “Subtotal” ELSE “Value” END

The fields 9, 29, and 21 are subtotals/total categories, so we are going to label them as such. Then, in our running total calc we are going to exclude these fields from being part of the running sum.

RUNNING_SUM(IF ATTR([Subtotal/Value])=”Subtotal” THEN NULL ELSE [Field Values] END)

This solution keeps everything in one sheet and allows you to still see those subtotal breakdowns. Thanks for reading! Please email me at spencer.baucke@gmail.com if you have any other enhancements or suggestions for this waterfall chart method.