Pivot Table Grand Total Issue

Hi

I have WinXP with Excel 2003

I have a pivot table that has a number of calculated fields in it.

One of the calculated fields looks at a column of data that has an amout in 
it and this is multiplied by another field that has a dollar figure in it.

Each line of data the Pivot Table calculates as normal. The problem I have 
is that the Grand Total works correctly for all the columns in the Pivot 
Table except for the ones that contain the dollar figures from the calculated 
field. The Grand Total line display a dollar figure that is approximately 10 
times as much as what it should be.

When I highlight the range in the Pivot Table it totals to the correct 
amount, yet the Grand Total line for these figures is completely wrong. There 
are five columns in the table that display a dollar amout and all of them 
have the wrong Grand Total.

This has taken me nay hours to create this table and this is the only part 
which does not work.

Can anyone help?


Thanks


John



0
JohnCalder (178)
4/2/2009 9:28:01 AM
excel 39879 articles. 2 followers. Follow

3 Replies
398 Views

Similar Articles

[PageSpeed] 18

John,

The Grand Total for the calculated item is not the sum of the individual calculated items, but the 
result of the calculationa as applied to the Grand Totals row. Pivot tables are wonderful at all 
things except calculations, and the easiest workaround for this is to do the calculations in your 
source table rather than in the Pivot Table.

HTH,
Bernie
MS Excel MVP


"John Calder" <JohnCalder@discussions.microsoft.com> wrote in message 
news:6DC91CB0-06FD-41E5-9A60-29571225DC0F@microsoft.com...
> Hi
>
> I have WinXP with Excel 2003
>
> I have a pivot table that has a number of calculated fields in it.
>
> One of the calculated fields looks at a column of data that has an amout in
> it and this is multiplied by another field that has a dollar figure in it.
>
> Each line of data the Pivot Table calculates as normal. The problem I have
> is that the Grand Total works correctly for all the columns in the Pivot
> Table except for the ones that contain the dollar figures from the calculated
> field. The Grand Total line display a dollar figure that is approximately 10
> times as much as what it should be.
>
> When I highlight the range in the Pivot Table it totals to the correct
> amount, yet the Grand Total line for these figures is completely wrong. There
> are five columns in the table that display a dollar amout and all of them
> have the wrong Grand Total.
>
> This has taken me nay hours to create this table and this is the only part
> which does not work.
>
> Can anyone help?
>
>
> Thanks
>
>
> John
>
>
> 


0
Bernie
4/2/2009 12:34:38 PM
Excel 2007 Pivot Table
Calculated Field
Here is an explanation of
and solution to the calculated field sum problem.
http://www.mediafire.com/file/gmumjmhrygy/04_02_09.xlsx

0
4/2/2009 3:24:53 PM
Thanks, that helped me overcome my problem.


John



"Bernie Deitrick" wrote:

> John,
> 
> The Grand Total for the calculated item is not the sum of the individual calculated items, but the 
> result of the calculationa as applied to the Grand Totals row. Pivot tables are wonderful at all 
> things except calculations, and the easiest workaround for this is to do the calculations in your 
> source table rather than in the Pivot Table.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "John Calder" <JohnCalder@discussions.microsoft.com> wrote in message 
> news:6DC91CB0-06FD-41E5-9A60-29571225DC0F@microsoft.com...
> > Hi
> >
> > I have WinXP with Excel 2003
> >
> > I have a pivot table that has a number of calculated fields in it.
> >
> > One of the calculated fields looks at a column of data that has an amout in
> > it and this is multiplied by another field that has a dollar figure in it.
> >
> > Each line of data the Pivot Table calculates as normal. The problem I have
> > is that the Grand Total works correctly for all the columns in the Pivot
> > Table except for the ones that contain the dollar figures from the calculated
> > field. The Grand Total line display a dollar figure that is approximately 10
> > times as much as what it should be.
> >
> > When I highlight the range in the Pivot Table it totals to the correct
> > amount, yet the Grand Total line for these figures is completely wrong. There
> > are five columns in the table that display a dollar amout and all of them
> > have the wrong Grand Total.
> >
> > This has taken me nay hours to create this table and this is the only part
> > which does not work.
> >
> > Can anyone help?
> >
> >
> > Thanks
> >
> >
> > John
> >
> >
> > 
> 
> 
> 
0
JohnCalder (178)
4/2/2009 8:38:02 PM
Reply:

Similar Artilces:

Office XP Exel
Scenario: User's A,B, C, and D all have access to an excel XLS on a 2003 server. Users A & B have modify rights, users C&D only have read rights. If user A updates the file, the general tab in properties reflect the exact time the file was modified. After user A saves and closes the file, user D goes in. The changes are there, but in the properties general tab, the modify date in an old date (probably the actual creation date). Is this normal ? Any idea's ? ...

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Excel template issues
I have a user that has a excel template on his network home dir NW 5.1 Server he says that after he creates a new file from that template - he goes to open and it tells him read only access running Excel 2000 thanks ...

Creating a table
There's probably an easier way to do it but... I have a series of numbers in column A (150 in all) and a series of letters in column B (22 in all). In total there are 4800 rows. What I am trying to do is create a table with the number down the left hand side and the letters across the top. In each cell within the table I need to count how many times the combination of number/letter appears. Eg: A B C 201 0 2 2 202 3 0 1 203 3 6 4 I tried combining the number/letter into a single text field using the CONCATENATE function then filtering ...

ACH total is incorrect
This is my third request for help and have yet to get a response, although I did receive a response by someone else having this problem too so I suspect it isn't just my clients. Since doing the 2008 year end update the direct deposit ach file only includes the company amount of the first batch included. If you include more than one batch the subsequent amounts are not included. It includes all the employee amounts from all batches, but not the company amount. The reports that print from GP are correct, but the actual ach file is incorrect. I can't find anything on knowledg...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Public folder issue, "messaging interface has returned an unknown error"
I'm running a SBS2003 domain and just added a public folder and a customized form for entry into the public folder. All of our clients (all XP Pro, Outlook 2003 with all updates) can get to that folder and use the form without any difficulty except for one. On one computer I get the classic "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." error message. I have tried a repair install, no change. I have tried making a new profile, no change. I have tried adding / removing his PST file (currently he's not even using a pst file...

How to write a new entry in a combo box to its underlying table
Dear Access 2007 VBA Gurus, I have a assets database (rather uncreatively named "Assets"). I use a form (named "Asset Acquisition Input Form New") to enter new assets. The "Manufacturer" field (combo box name "Manufacturers_ID) on this form is a lookup to a Manufacturers table. What I want to happen is when I enter an item that is not in the lookup list, I want a message box to prompt me to add the new entry to the underlying table, or to cancel and select an item from the list. I have no trouble with the MsgBox command itself. What I don&...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

groups detail section totals access 2003
Hi all, I know this can be done, but haven't figured out how yet. I have what basically is a summary report that my sql comes up with for the detail rows. I want to total these rows in the report and display immediately below the detail section. I don't really want to group anything, but want to treat the whole detail section as a group. That being said, how can I get a "group footer" on the designer so I can add my total columns. If I use "sorting and grouping", it starts grouping things and that is not what I want. I don't want to use the "page foo...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

FP: Couldnt close table
We have been receiving this error on two separate PCs after a recent upgrade to 7.5. Our version is 7.50g43 (service pack 5). At first we thought it was isolated to one PC now a second PC is having the same error. On the first PC, I ran new network cable, installed a different network card with no help. This PC is Win98, 64MB RAM, 600Mhz. The second PC has 128MB RAM also Win98. I don't believe switching to XP is an option right now. This message has appeared in the payables module on both machines. But it has also appeared in receivables as well. Not in GL or Payroll. I...

Terminal Servers in Cluster - Login/Profile Issues
Hello Gurus, Currently I am having an issue with logins and profiles as per details below. Server/Network Configuration Details: 1 X Windows Server 2008 Std FE 64bit (DC) 2 X Windows Server 2008 Ent 32bit Terminal Servers User Profiles are Roaming and exist in: \\SBSERVER\Profiles on the Domain Controller and C:\Users\ on the Terminal Servers. Approx 50 users. Problem Description Error messages when logging in the terminal servers: Your roaming profile is not synchronized correctly with the server. Windows will load your previously-saved local profile instead. See the pre...

Same Table cannot be the child table in two nexted relations...
I've been setting up a schema with the XML Designer in VS .NET 2003. The designer lets me set up a lot of things including a complex type that contains an unnamed complex type called modified. When I try to preview the dataset, I get an error described as "The same table (modified) cannot be the child table in two nested relations. I've run into this before with a different complex type, and I've changed the name of the instance of the type in the various elements it's used, and the problem goes away. In this case, problem is an unnamed complex type, so it only a...

Pivot tables and Macros
I was looking to be able to manipulate (ie change selections from the drop downs, not change the fields in the table or anything)and print from a pivot table using a macro. So how do I go about doing that? When I set up a macro by recording the actions that I want to do, I always get an error message when I try to run it as a macro. What specific steps are you recording, and what error message do you get? Can you click the Debug button, and see the line of code that is causing the problem? Dust For Eyes wrote: > I was looking to be able to manipulate (ie change selections from the >...

Issue with Loan Accounts
Hi Everyone, I have been having this issue for a while - but just now trying to figure out what's happening. I have several loans (including car, equity, etc.) which I have set up on Money 7. Sometimes the interest and principle are calculated correctly - but more often, the entire sum of the payment is allocated to principle (as viewed in the account register). I've check to insure that the loan is set up okay - and it looks okay to me. Any ideas what's happening to me? Any help appreciated. Thanks. Patrick Hi, I was really hoping someone would respond to this. My ...

How to copy aQuery to a new Table?
I have a database in a Table, a report based on that same Table and a Query based on that Report. After two months or so I like, after some new data input, to save the Table into a new Object Table. What is the best way for the Report and Query to follow the new Table whitout recreating the original Report & Query? Thankyou for your comments. I use MS Office Access 2007. Joe T >>I have a database in a Table, a report based on that same Table and a Query based on that Report. Your phrasing is wrong when it comes to the elements of an Access database. A dat...

Pivot Table in Excel
hi, I have a problem using the pivot Table in excel 2000. Earlier the location of the pivot table in excel was pointing to say c:\sales.mdb. Now the location has changed to D:\Sales.mdb. someone please tell me the place to change in the excel to reflect the same. So that upon refreshing the document i can see the latest data. Currently i am getting a error message when i try to refresh, but its not prompting to change to alternative location. If i edit the excel file in a notepad...i can see the location pointing to c:\sales.mdb. Thanks in advance for any help regarding this..expect...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Multiple Language Issues
Hi all, Has anyone successfully manually updated a non-English supported 5.5 to 2003? (The reason for the manual update was a broken admin account.) Mail and contacts were all exported to PST files, and then imported to 2003. However, although the text of the messages is fine (English and Russian), the text for contact names and message subjects gets gorched if they contained any Russian text. Does anyone know the correct sequence to follow to take messages and contacts in Russian (or other languages) from 5.5 to 2003, while retaining the non-English subject lines and contact names? ...

Calculate Subreport totals in a main report Group footer
Hi all Apologies if this has been answered before but I can’t find it. I have a main Report with a Group called “Product_Category” which lists a number of “Products” in the Detail I have a Subreport named “product_costs” which has a record for each date and Text Boxes named “materials” and “fuel” (there are more but I’ll keep it simple). The Subreport sums all costs and has Text Boxes named “summaterials” and “sumfuel” in the footer (all with a height of 0.1cm) The Subreport is embedded in the Detail of the Categories and linked by Product_id In the Detail of the Main Rep...

pvt table field settings!
hi! i am receiving data every day from 10 different places as under.! for example: DATE 11.01.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.01.2009 PLACE "B" TOTAL NUMBER OF RECEIPTS 50 TOTAL OF RECEIPTS AMOUNT 1500 DATE 11.02.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 15 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.03.2009 PLACE "C" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 -likewise i'm receiving daily data from all the 10 places..! what i want is a pivot table report on a...

excel 2007
I have data that looks something like this account#, invoice amount 1, 50 1, 70 1, 80 2, 10 2, 50 2, 52 2, 50 3, 10 4, 30 I need a way to combine all the invoice totals for each account so I have results like this 1, 200 2, 162 3, 10 4, 30 Any suggestions on how to go about doing this? I thought I had done something similar to this with filters before in Excel 2003, but I can't figure out how I did it. Thanks! You can use subtotal or a pivot table in the pivot put the account numbers in the row field and the invoice amount in the data, in subtotal use at each change in account numb...

Money 2005 Password Issue
I have been using Money 2005 for about 8 months and suddenly I cannot sign in using my usual hotmail.com passport. The error message is "the e-mail address or password is incorrect". However, I can sign in using the same e-mail address/password pair to hotmail and MSN Money in IE. I've tried KB Q331123 but no luck. Any ideas? More details: 1) I can get into my Money file in offline mode 2) I even tried to change passport password. The new password works in IE, but not Money. "MM" wrote: > I have been using Money 2005 for about 8 months and suddenly I cannot...