7 Replies Latest reply: Nov 10, 2012 10:50 AM by Sunil Chauhan RSS

    Problem with column totalling when using If in expressions

      Hi Folks,

       

      Need some help on a bit of modelling I'm trying to do.  Basically I want to forecast 2nd, 3rd and 4th quarters based on the results of 1st quarter.

       

      I have two variables which are used for the forecast, medicalGrowthFactor and surgicalGrowthFactor.  These need to be applied based on the text in the "Medical/Surgical" field.

       

      The Q1 Actual column is a Sum().

       

      The code to calculate each of the forecast columns is :

       

      If([Medical/Surgical] = 'Medical', //If it's medical
                 ([Q1 Actual]+([Q1 Actual]*(medicalGrowthFactor/100))) //Apply medical growth factor
                 , If([Medical/Surgical] = 'Surgical', // Else, is it surgical?
                  ([Q1 Actual] +([Q1 Actual]*(surgicalGrowthFactor/100))) //Apply surgical growth factor
                  , [Q1 Actual])) //Otherwise return Q1 Actual
      

      This works just fine and all the values I get are exactly what I'm looking for, BUT the problem I'm having is for the column totals, using 'Show Partial Sums' in pivot.  The totals all match the Q1 Actual i.e. it's not totalling the values in the column!

       

      If I change the code to below

       

      If([Medical/Surgical] = 'Medical', //If it's medical
                 ([Q1 Actual]+([Q1 Actual]*(medicalGrowthFactor/100))) //Apply medical growth factor
                 , ([Q1 Actual] +([Q1 Actual]*(surgicalGrowthFactor/100))) //Apply surgical growth factor
                ) 
      

       

       

      The column total shows factors in the surgical growth but not the medical.

       

      Any suggestions??  My brain is melting.....