Problem with column totalling when using If in expressions

[#1]

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.....

[Gysbert_Wassenaar]

Did you already change the Total Mode from expression to Sum of Rows? Expression Total doesn't work if you use expression names (or column references) in your expression.

[#3]

Can't do that as the first column is also an expression.  Thanks for the thought though.

[SunilChauhan]

while calculating expression using if some time show you not corect result .because you applied suprees null on dimesion. if you remove the supress null check in dimension then it should match.

[#5]

Total mode is not changeable for me on the expressions.  It's greyed out.

[Gysbert_Wassenaar · Accepted solution]

Ah, that's sounds like you're using a pivot table. Any reason it must be a pivot table or is a straight table a possibility?

[SunilChauhan]

no issue

you can convert first pivot into staight and check total in expression tab

and again convert into Pivot table.

hope this helps

[#8]

Great, converted to straight table and changed total method.  All good now.

Thanks Mate