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.
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.
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
And the Calculated field to:
= EffectiveUnits * 200