Excel Pivot-Table calculated fields with if statement return wrong results

[John2009]

Hello All,

I'm new to this nice forum and have the following question/problem. I hope someone could help.

In a simple Excel Pivot table the calculated fields return wrong values. Tested on Excel 2007 and Excel 365.

For comparing purposes I've added the columns Analysis and Develpement with IF statment in the Excel table.

What I would expect is that it should be Analysis = CFAnalysis and Develpement = CFDevelpement.

Do you have any ideas why the calculated fields behave like this?

Thanks
John

 

[RoryA]

The values are actually correct given how calculated fields work, which is at the aggregate level. They always SUM any fields you use in the formulas before they do anything else. So your first formula is effectively:
=IF(SUM(Type)=1;SUM(Hours);0)

 

[John2009]

The values are actually correct given how calculated fields work, which is at the aggregate level. They always SUM any fields you use in the formulas before they do anything else. So your first formula is effectively:
=IF(SUM(Type)=1;SUM(Hours);0)

Hi Rory,
thanks for your answer. I'm not sure, if it is so as you describe. There are some examples on the internet, which show calculated fields working the way I would expect. For instance
Excel: Use IF Statement in Pivot Table Calculated Field
I can't figure out, where the difference between my approach and the one in the above link is.

If calculated fields work really like you describe, I don't find it usefull. In my openion the aggregation should work on each level.

John

 

[RoryA]

You should read that linked article again, paying attention to the helper column. That is required so that the sum of the helper column is either 0 or non-zero, and that allows you to distinguish between the types.
Calculated fields work exactly as I described, whether you find that useful or not.

If you are working with 365, you can use the data model and power pivot where you can create measures that can do just about anything you can think of. For 2007, you will be stuck with using helper columns in your source data.