Creating a Variance Waterfall chart

  • 12 December 2022
  • 0 replies
  • 236 views

Userlevel 4
Badge +6

​A variation Waterfall explains the difference of 2 or more dimension items in columns across another dimension or set of metrics in rows. The native variation waterfall functionality of Pigment doesn't need any prior calculation steps or modeling in order to calculate the variance values.

Setting up the table view

Good to know #1: The dimension items you want to compare always need to be in the columns of the pivot. The dimension/metrics that you put in the rows of the pivot will be the different parts of the waterfall bars.

Good to know #2: When using metrics in your rows, make sure they have the same dimensions. Having different dimensions by metric can cause unexpected results in your chart.

Good to know #3: If you’re using metrics in the rows: don’t worry too much if your metrics dont sum up perfectly. By using the Balance Data functionality Pigment will put the missing part in a bucket named “Other” (see bottom of the article for more details).

In this example, we're creating a waterfall to compare the EBIT of 2 months (Nov 22 & Dec 22). To set this up we will make a selection of metrics from the Income Statement (in rows) and we hide all metrics that are not summing directly into our EBIT metric (e.g. Gross Margin %) and also hide metrics that can cause double counting (Gross Margin, as this result is already captured by summing Revenue & Cost of Goods Sold). When you are using another dimension in your rows (instead of metrics) you don’t need to hide anything.

 

 

xq4Uzql06-2wDP0Fu7B_X_OsSNQB_prgDthyrUzXA7xuE72sZlmVxJv2GUChyt_TdMhr9axg51AKTcS40wlH4mIhyt4dhnb1Zb-mSxkKuBu4Y3OV-uG5PIYzu5XS2sj50uVF_vsPdrYRp8cy7EI39wn65hDbBRoHLslf30Wecg_vwNKqDhPo2gTuH6QXWQ

wshEu4xm3AsGreKr-zmQd6jmJAndpdu0CvRnKNMondJDqGQh3EBnR74BOlI-nyJu263rFwy5CTDTsDVd7GcgGUeKRGTBxrYTQ24Rq0kXkxCI_6w5MfEEGtlt0phCgcntCUt7G9S63P7WOwx4bNzr7D4HJUATZouqe-piu8E3uv-jSVbYV2uQxxVDborN0w

j2XbFdg4bJeorBhVU9MIVBrWv6vyIYozDco1s92eGBewOEKmfSci7Id6-4D5tb44SJNAKdjYPupXMFBtu1Ch0VA-treiS52bx3U5umZYHJtEx790ghnlAXbJ2qbgcTGqCnEaQpAuf6VxFHzWvojlRKectMku2Bu5NJ7KIOJ9wfPRlmcG7ym7opWviao_gg

XAA7Nw9N1f9yiLdj21x493YMGzmVH2TNT9ii8Wek0Kyk4DD6FAseofEDkYk-6HRbGWer67tGgf4cUzVOvrQsBa7bBm8wKY91bvNGc14GVwEA4QpDFYQ8tYsjrx0ZPT0yDpJJ2HdtFRUywRnD71yy-FQZ5K9uJv_uFIZfGRKZ1sTJZf_XxNWQu6dsXCh2gg

Preparing the chart

Now that our view is prepared we can start the next step. Convert the table into a chart by selecting the Chart option and format it as a Waterfall chart (Type Variation).

 

czqw4gEeTp5ldKRQ5aCAeYCfilTTR8-KkDUnRu4mrgI0bEZXjXBDj5D5lEorKcHiBTb0NjT8dQPAhf9YX9Ze8eubR3iN7PYAI6wqRzKgxugENcwcNc4v92WMxL7IWEQwjRvOeMRCwIKwzX18hTqNnmKBxX_CIFtMLI9xgnlxVAQgZ-ZGql-McLRARE8iQw

sJO3OgbgGyCtSGF8e83REKRFjHOdSoZH-n-9xHNtnJyPIEWhwYvHMW8hhuo9Xg384tQrF6El-EtjsZHCCNJYHac-YAgnj8xndMZZIKbbhM3rgcrOujOEF-dAmYzxCTuyubBMevpwZsFAHJfH3twLtz1UucnCCUIT3QI94Z3WPccH4dGFJ0YPaRFbRSaNMA

 

Pigment will already render your waterfall chart based on the view. There are a few more (optional) options to set to make your waterfall even better! The most important options for a variation waterfall are listed below, other generic options can be found in this article about generic waterfalls:

  • Balance Data: if for some reason your waterfall isn't summing up correctly (probably because you are missing some rows in your view) you can let Pigment create an “Other Bucket” where it will plot the difference between the 2 (or more) dimension items you are comparing.

  • Auto Scale: by default, the Y-axis starts at 0. To make your graph visually more attractive and informative you can turn on this option so that the Y-axis starts just below the first comparison item.

  • Dynamic selection of columns: amaze your end-users by making your waterfall as dynamic as possible! By showing the dimension in your columns also in your pages you can enable every user to select 2 (or more) dimension items to compare! In this example, we’ve published the Month dimension also in the pages for dynamic selections by user.

Alternative options for rows & columns

Please note that in this example the rows consist of metrics. This is not mandatory, you can also replace the rows by any other dimension. E.g. a Company dimension if you want to split out the EBIT difference between Companies across 2 months. The column dimension can also be replaced, e.g. by a Version dimension to create a view to compare Actuals vs Budget by Company.

 

Publish the chart and amaze your Pigment users with this variance visualization! 


0 replies

Be the first to reply!

Reply