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

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

• ###### Re: Problem with column totalling when using If in expressions

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.

• ###### Re: Problem with column totalling when using If in expressions

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

• ###### Re: Problem with column totalling when using If in expressions

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.

• ###### Re: Problem with column totalling when using If in expressions

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

• ###### Re: Problem with column totalling when using If in expressions

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?

• ###### Re: Problem with column totalling when using If in expressions

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

Thanks Mate

• ###### Re: Problem with column totalling when using If in expressions

no issue

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

and again convert into Pivot table.

hope this helps