1 Reply Latest reply on Mar 7, 2015 7:14 AM by KK Molugu

    Waterfall Chart for Variance Analysis

    Fabio Italiano

      I have the following calculated fields:

       

      Prior Year Gross Sales:

      IF

            DATEDIFF('year',[Date],TODAY())=1

         AND

            MONTH([Date])<=MONTH([Max Date Parameter])

      THEN

          [Gross Sales]

      END

       

      Prior Year Gross Units:

      IF

            DATEDIFF('year',[Date],TODAY())=1

         AND

            MONTH([Date])<=MONTH([Max Date Parameter])

      THEN

          [Gross Units]

      END

       

      Prior Year Average:

      SUM([Prior Year Gross Sales])/SUM([Prior Year Gross Units])

       

      Current Year Gross Sales calculated the same way as Prior Year Gross Sales substituting =1 with =0

       

      Current Year Gross Units calculated the same way as Prior Year Gross Units substituting =1 with =0

       

      Current Year Average:

      SUM([Current Year Gross Sales])/SUM([Current Year Gross Units])

       

      I then calculate the Price Variance as:

      ([Current Average Price]-[Prior Year Average Price])*SUM([Current Gross Units])

       

      and the Volume Variance as:

      [Prior Year Average Price]*(SUM([Current Gross Units])-SUM([Prior Year Gross Units]))

       

      How do I build a waterfall chart where I have:

       

      the first bar as PYTD

      the second bar as the Volume variance from PTYD

      the third bar as the Price variance from the Volume Variance

      the fourth column as the YTD?

       

      Thank you for your help