Excel pivot table calculated field total returns unexpected value

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.

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 ```

Category:excel Views:1 Time:2009-11-16

Related post

• excel pivot table calculated field 2014-03-04

If I have a table with col1 that has number values. Then in a pivot table I would like to count the number of rows that the value is greater than 5. I tried creating a calculated field like this =COUNTIF( Col1 ,">5") But this causes an error. any

• 2010 Excel Pivot Tables - Calculated Field... 2014-12-02

I have a Pivot Table summarizing Sales and CofGS for Salespeople. Data file does not have Gross Margin, because I could add to the Pivot Report as needed. In Calculated Field input entered a name GM, then in the formula box entered = Sales-CofGS (bot

• Excel 2013 - pivot table calculated fields - referring to other dimensions in calc 2014-08-15

I'm struggling to find much useful documentation on Excel 2013 pivot table calculated fields. My pivot table is fairly simple. I have a 'Company' dimensions as rows (e.g. 'A', 'B' etc) and calendar weeks as columns ('1' --> '52'). The value in the

• Can a pivot table calculated field's formula reference the row value of another field in the same pivot table? 2014-04-18

I would like to be able to use the following formula in a pivot table calculated field: =IF('Cost Type' = "Internal Labor Cost", 'Jan Delta' / 78, 0) However, the result is always zero. Is there any way to reference the row value of another pivot tab

• Pivot Table Calculated Field results in error when data updates 2013-03-17

Has anyone any suggestion as to why a pivot table returns the Name error in calculated fields after additional columns are added to a database. To recreate the problem: a) Create a simple database - Field names: Product, Quantity and Price; b) Insert

• Pivot table calculated field returning incorrect results 2015-02-28

I am using a pivot table with a calculated field to create a headcount turnover report showing the % of staff that are new to a given department. The report then subtotals at functional area, region, and grand total for the company. The formula is %N

• How to use a VBA custom function in a pivot table calculated field? (Excel 2010) 2015-03-05

Although I suspect the answer is that I can't. :-( I'd like to use a custom function to calculate a score based on two other fields in my data. The two fields might be, for instance, an identifier for a question (Q1, Q2, Q3, ...) and another for the

• Pivot table calculated field, how to get a % weighting by subtotal grouping? 2013-12-04

In a pivot table I know you can get a % of a row to get a weighting as a % of 100% on that line, but is there a way to do that same calc vertically? For example if you organize data by Sales people, but want to do an individual weighting of the produ

• create chart on the fly from pivot table calculated fields 2015-02-23

I have created a dynamic 'data table' which feeds info into a pivot table, which nicely summarizes the data The type of data I am working with is the number of hours worked and the number of service units billable. I am using this info to generate pr

• Pivot table - Calculated field 2012-01-23

Is it possible to create a pivot table that sums all fees and then create a calculated field that sums the fees based on a another fields requirements? For example: =IF('Type'="Associations",Sum(Revenue)) - I can't seem to get the 2 to work in the sa

• Excel - Pivot tables - calculations after summarizing with Averages 2012-11-12

Hi I have created a pivot table and changed the Values so that the fields are "Sum of" (which is a financial value of contracts we hold with each organisation and "average of" which is the average of their annual turnover - I created these in Values

• Pivot Table Calculated Field: % of Subtotal 2014-05-04

I've created the above pivot table and no matter what I do I'm unable to create a calculated field in column D of the pivot table. In cell D5 I am trying to get the percentage: C5/C8, In cell D6 I'm trying to get the percentage: C6/C8, etc. I can cal

• Pivot Table Calculated Field Problem 2014-06-26

I read a few questions about Calculated Fields and it seems like there is a problem here! Still, maybe I am missing something so I'll ask my question anyway. I don't want to add another column in the data file as the multiplication of 2 entries on th

• Excel Pivot Table - create field taking on values based on how another is formatted 2015-08-28

First of all, thank you for taking the time to read and hopefully respond to this. I am working with a pivot table that has a field (A) which is shown as the difference from two other fields, (a & B), i.e. A=a-B. I want to create another field that t

• Not all calculated fields total in pivot table 2014-06-11

1) I run a basic pivot and wind up with this: Name Sum Of ActualPay Sum of MaxAllowedPay John \$100 \$150 Mike \$150 \$125 Total \$250 \$275 2) I create a calculated field ("OverUnderMax") to find the delta between the two Name Sum Of ActualPay Sum of MaxA

• How to convert a text field into a number field from an Excel pivot table 2011-06-10

I have an Excel pivot table which is dynamically generated by a third party program. One of the fields brought in is of type text, but contains a number. I have tried setting calculated field = VALUE('Field Name') but it always returns zero. Not sure

• Excel Pivot Tables: How can I display multiple grand total rows? 2009-10-05

Excel Pivot Tables can display a grand total row that, by default, sum each column's data. I would like to display multiple grand total rows that display each column's count, average, standard deviation, and standard error. Does anyone know how to do

• Excel Pivot Table Row Labels Not Refreshing 2009-06-15

To all, I have an Excel spreadsheet pivot table that is linked to an SSAS data source. The employee counts are aggregated and return the following values: Row Label Count North America 7 South America 2 Europe 5 Asia 1 Australia 3 Let's say the user

• Removing the timestamp from a date in an Excel pivot table connected to a cube 2012-01-18

So I have a cube created in Analysis Services 2008 R2. The cube has some dates on one of the non time dimensions. When I put the date on the filters in the excel pivot table, it always puts the date as, for example, 01/18/2012 00:00:00.000. Is it pos