Excel pivot table calculated field total returns unexpected value

Question

I have an Excel spreadsheet which has a pivot table on it. The data itself is quite simple, Number of Units and a Discount (percentage from 0 to 100), pivoted on date across the top and customer down the left hand side. Those numbers are straight from a SQL view, so the pivot table source is just:

SELECT * FROM UnitDiscountView

All was looking fine until I attempted to add a calculated field of TotalCost (Each unit will costs £200) to the pivot table:

= (200 *NrUnits ) * ((100-Discount)/100)

The total at the bottom was negative, and vastly more than I was expecting as a number. If I select all the cells, then the sum which appear in the status bar at the bottom is what I would expect, but the Total field at the bottom is over a hundred times bigger, and negative to boot.

I am stumped. I have searched since Friday without finding anything which will help me to solve this. Any pointers on where to start looking for the solution would be greatly appreciated. I should mention that SQL is my forte, not Excel. I haven't used Excel's Pivot tables before last week, so I am probably missing something really obvious.

Answer 1

Calculated fields will work with the totals:

200 * (Sum of NrUnits) * ((100 - [Sum of Discount]) / 100 )

So for your total row, if Sum of Discount is greater than 100 (adding up all the Sums of Discount on the pivot table), the part of your formula (100-Sum of Discount) will be returning a negative number, hence the negative value.

What I normally do is to add another column to the table to the right of the values returned from the query. I will add my formulas there, then do the pivot table on the resized table.

If you have formatted the returned SQL query as a table (or list in Excel 2003), the pivot table source range should even resize by itself.

Answer 2

I found the answer.

Excel was effectively doing this for the total:

= (200 * sum(NrUnits)) * ((100-sum(Discount))/100)

Which isn't even close to what I wanted:

= sum((200-NrUnits) * ((100-Discount) * 100)))

I got round it by changing the SQL from the view to be:

SELECT *, NrUnits*((100-Discount)*100) AS EffectiveUnits
FROM UnitDiscountView

And the Calculated field to:

= EffectiveUnits * 200