 SOLVED

# Calculated Field in Pivot Table Not Summing Correctly

Hi, all. I have a pivot table that has a calculated field in the last column of the pivot table. The formula I am using is an IF formula to determine Full Time Equivalents. I was hoping it would add the FTE's and give me a total at the bottom but it just says "1" in the grand total section. It should say 91. Any thoughts on how (or if) I can correct this? Thank you! 6 Replies

# Re: Calculated Field in Pivot Table Not Summing Correctly

An image doesn't really show much other to help in diagnosis....is it possible for you to post a copy of the actual spreadsheet, just omitting real names of real people (and any other identifiable data)?

# Re: Calculated Field in Pivot Table Not Summing Correctly

Right-click PivotTable | Refresh, maybe? It's difficult to say without seeing the workbook.

# Re: Calculated Field in Pivot Table Not Summing Correctly

It occurs to me to ask also what the IF formula is testing for, and what its result is in connection with the FTE question. But truly, most helpful: post the actual workbook, devoid of confidential data.
best response confirmed by slohombre (Contributor)
Solution

# Re: Calculated Field in Pivot Table Not Summing Correctly

With calculated field in total PivotTable uses the same formula as for the rows, not sum them. Thus if your IF() applied to totals returns 1, in grand total for FTE will be 1.

For such calculations it's better to add data to data model creating PivotTable and use DAX measures for aggregations.

# Re: Calculated Field in Pivot Table Not Summing Correctly

@Sergei Baklan Would SUMX be the right DAX formula?

# Re: Calculated Field in Pivot Table Not Summing Correctly

Perhaps, it's hard to say without knowing the model. Here When to Use SUM() vs SUMX() - Excelerator BI is the great post which explains and demonstrates difference between SUM and SUMX