PivotTable - Totals & Sub Totals Query

Hi,

I'm using a pivot table with 2 columns of data.  1 col. is just a 
straight item count while the other is a calculation of the item * 
price (where price and item are cols. on the source data table.  The 
subtotal for the "item" col. is OK but the subtotal for the calculated 
col is wrong (it is in fact the sum of the item col. * sum of "price" 
col. from the osurce table.

Any ideas how I can make the subtotal be the expected sum of the 
calculated col.?

Cheers,

Frank.

0
7/7/2005 11:18:05 AM
excel 39879 articles. 2 followers. Follow

1 Replies
388 Views

Similar Articles

[PageSpeed] 23

To get an accurate total, you can multiply item * price for each row in 
the source data table. Then add the Total column to the pivot table.

Frank wrote:
> I'm using a pivot table with 2 columns of data.  1 col. is just a 
> straight item count while the other is a calculation of the item * price 
> (where price and item are cols. on the source data table.  The subtotal 
> for the "item" col. is OK but the subtotal for the calculated col is 
> wrong (it is in fact the sum of the item col. * sum of "price" col. from 
> the osurce table.
> 
> Any ideas how I can make the subtotal be the expected sum of the 
> calculated col.?


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
7/7/2005 11:27:42 AM
Reply:

Similar Artilces:

PivotTable
Hi, Is it possible in some way to fix the structure of a pivot table in excel or even in Access so that I have a familiar layout even though the data may vary from differences in the data. By implication, thefore to show empty colums/rows somehow. This is very important to my situation. I couldn't think of any way of changing the format of the pivet tables. However to solve your problem, you could have your own format in a different sheet (within the same file) and link the pivet table using VLOOKUP( formula to get the numbers to the right place. You could use refresh data to upda...

Query as Pop Up, is it possible?
Hello there! Is there an option, or how can I run a Query as Pop up mode? What I want is that the user can see the results in Crosstab query but I want they do not have access to see the query in design mode neither make changes, I would like a pop up query but not a maximized window and the only option that the user can see is the Close Botton, and another button to print the query, is it possible? Note: using sub-form does not work since in the crosstab appear some main field that when the sub-form was built they were not there, and this cause the sub-form show an error because th...

pivottables
hi, I created a pivot from a dataset. This was no problem at all. In a later stadium I changed the recordset where the pivottable is based on. For example i replaced all the records containing pete into mike in the column <NAME>. When refreshing the pivot it now contains both mike and pete. Pete is redundant, because i no longer exists. How do I get rid of it?. lennie Hi Just drag the field out of the Pivot Table, then do a Refresh, then drag it back again -- Regards Roger Govier "lennert" <lennertpronk@gmail.com> wrote in message news:1140180768.525075.3081...

Does anyone know the PivotTable Refresh Event? #2
Thank you Debra it worked fine for me -- JohnD'Eli ----------------------------------------------------------------------- JohnD'Elia's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1531 View this thread: http://www.excelforum.com/showthread.php?threadid=26930 You're welcome. Thanks for letting me know that it worked. JohnD'Elia wrote: > Thank you Debra it worked fine for me. > > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

query update
I have a query that is derived from 3 tables with relationships. i want the user to open a form and be able to change data in that query. i'm assuming it is not letting me because it is derived from more than one table. is the only solution to this is to create a table off the query and then base my form on that table. wasnt sure if there was a different way around this. thanks DD wrote: > I have a query that is derived from 3 tables with relationships. i > want the user to open a form and be able to change data in that > query. i'm assuming it is not letting me ...

How do I add new data to a pivottable/chart?
I have a spreadsheet that I add new data to weekly. Is there a way to have the pivottables/charts refresh to include this data without recreating them? If I try to do extend the range of cells used to create them originally, I am unable to group dates (which I need to do) on the chart because of blank cells. You can use a dynamic range as the pivot table source, and it will automatically expand as rows or columns are added. There are instructions here: http://www.contextures.com/xlNames01.html When you refresh the pivot table, all the new data will be included. Brian Lee wr...

Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook?
Wow! I tried this out, and it seems to work beautifully. It's odd, though, that I haven't seen this technique mentioned in any of the several Excel references that I've looked at. Basically, I have a workbook with several worksheets...one worksheet contains a large list with all the records. I wanted to set up the other worksheets to have certain functions...I wanted them to use only certain columns from the main list, and to contain only certain records from the main list that met specified conditions. This is the best solution I have found so far - i.e., creating database q...

Any Way to invite Outlook Calendar Sub-folders through the GAL?
We have sub-folder calendars set up within existing shared mailboxes. Is there any way to get a link to those calendars directly, so that they may be invited to a meeting (as a resource)? No. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Jay" <Jay@discussions.microsoft.com> wrote in message news:F5CE313B-7598-4A48-9460-A5A9B641BAEA@microsoft.com... > We have sub-folder calendars set up within existing shared mailboxes. Is > t...

Query Optimisation problem. CTE with Ranking function used by12 x union stm
Hi guys, I hope you can help me with this one and get this query to run a bit faster. I have a CTE which looks at data in the table and gives it a ranking number using the rank() over (partition by ?? order by??) syntax. This is done because I then need to run some DQ tests on the data, based on the ranking order of the records. (UK NHS RTT status codes for anyone who is interested...) CTE statement is below. [code] with CTE as ( select R2T.Refrl_refno, rank() over (partition by R2T.Refrl_refno order by R2T.RTTST_Date,R2T. SORCE_Refno) as 'Rank', RV...

Web Queries
I'm interested in learning how to customize web Queries. Is the onl way to do this is to use XML? I tried using the Data-> Import Externa Data-> Web Query tool but I'm stuck with the layout thats on the we site. If anybody has any links to any additional literature that would b great as well. I looked in my excel bible but it only shows how t pull the data & not customize it. Thanks -- Phatchef2 ----------------------------------------------------------------------- Phatchef24's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1093 View thi...

dynamic sub menus without command ids
Hi, I am trying to create dynamic submenus without predefined command ids. Having overridden OnMenuSelect, I dynamically generate the menu items (when the correct menu to be opened), replacing an original dummy entry with the id ID_DAR0: int c = pPopupMenu->GetSubMenu(MenuDARSubSub)->GetMenuItemCount(); for(int x = 0; x < c; x++) pPopupMenu->GetSubMenu(MenuDARSubSub)->RemoveMenu(0, MF_BYPOSITION); for(int m=0; m<=m_pDoc->m_namenD.GetUpperBound(); m++) pPopupMenu->GetSubMenu(MenuDARSubSub)->AppendMenu( MF_STRING, ID_DAR0, m_pDoc->m_name...

PivotTable data...
How do I use more than one Summary function for a PivotTable data field? In the Pivot Wizard, add another copy of the field to the data area Double-click on the new field, and under 'Summarize by', choose one of the functions. SkoolGirl wrote: > How do I use more than one Summary function for a PivotTable data field? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Trying to Siplify a Query in MS Access 2003
I am a real Novice to MS Access 2003. I am trying to create a query to populate a form to be used as a subform in another form. I have created the form that use a table view of the a table. But would like to be able to use text boxes to view the data. I do not know if I should change the table designs that I am currently using. Here is what I have. 1. t_Contacts Table: ContactTabID (Autonumber) ContactID (Text value) FirstName (Text value) LastName (Text value) Birthdate (Date/Time value) ContactTabID ContactID FirstName LastName Birthdate 001 DonDuck ...

Query General
Hi. I have two tables Table1 Name Serial Date John 01-01-2010 Tony 11-02-2010 Table2 Name Serial Date John 05-04-2009 John 06-08-2009 John 01-01-2010 Tony 11-02-2010 Tony 11-02-2010 I want is to assign the serial against each name starting from 1 in table2 according to date in ascending order and the max serial should be assigned against each name in Table1 i.e.. Table1 Name Serial Date John 3 01-01-2010 Tony 2 11-02-2010 Table2 Name Serial Date John 1 5-4-2009 John 2 6-8-2009 John 3 1-1-2010 Tony 1 11-2-2010 To...

PivotTable Column Field Values
Hi there The column field list of my PivotTable (when one clicks on the drop-down arrow) is populated with a list of items which no longer exist in the data table from the data is drawn. Is it possible to reset these values so that only valid data can be selected. Please let me know if I have not explained the problem adequqtely. Regards Michael There are instructions here for clearing old items from a PivotTable dropdown list: http://www.contextures.com/xlPivot04.html Michael Rekas wrote: > The column field list of my PivotTable (when one clicks on the > drop-down arrow) ...

Changing RecordSource Causes A Reload of Sub Form Destroying References
Apologies for posting in two newsgroups but after I originally posted it in forms I realized this should have been posted here (formscoding) instead. Access 2007 This only happens on some machines. It has occurred on a PC running Windows XP SP3 and a MAC running Windows. I have been unable to reproduce it on other PCs running Windows XP SP3, Vista or Windows 7. On the machines that have the problem it always occurs. I have a form with a sub form control. The control's SourceObject is changed depending on the function required. On the forms that is used in the control is a...

pivottable
Dear all, Is there anybody how knows what the shortcut is to deselect fields in a pivottable? (I have a pivottable with 1000 possiblities for a field, but I only need some of them) Best regards, Jan Excel 2002 has a Show All checkbox to show or hide items in a field. For earlier versions, you can use code. There are examples here: http://www.contextures.com/xlPivot03.html Jan wrote: > Is there anybody how knows what the shortcut is to deselect fields in a > pivottable? (I have a pivottable with 1000 possiblities for a field, but I > only need some of them) -- Debra Dalg...

PivotTable canned functions
I am using Excel 2000 (9.0.2720). In my work I often faced with the task of creating ratios of CurrentListValue / CurrentSaleValue then need to perform various calculations on those ratios to identify and conclude on Central Tendencies (among other things) within categories from a parent array. The list of functions available for PivotTable calculation (I am assuming under the hood these are Array Functions) is very narrow. Only providing for Average, Standard Deviation, Min, Max, etc. Is there a way to add to this list, such as but not limited to, Average Deviation, Median, Geometr...

PivotTable ?
I want to ask a general question ? What is the use of a pivottable ? Why is it called "pivot" ? Actually, I can do the analysis on the original table .. Thanks On Mar 13, 1:52=A0pm, "Carlson" <davidcarl...@yahoo.com> wrote: > I want to ask a general question ? What is the use of a pivottable ? Why = is > it called "pivot" =A0? =A0Actually, I can do the analysis on the original= table > . > > Thanks Hi Carlson, a Pivot table is a very useful tool to analyse data columns. The name stems from the technical field: a pivot...

Multiple Sub Domains with own Catch-All Mailbox?
I have a client who wants to create multiple email sub-domains (no problems settiung that up) but then has requested that each sub-domain has it's own catch-all mailbox for unresolved emails. Anyone have any pointers? Thanks Andy On Tue, 11 Jan 2005 05:21:04 -0800, Andy H <AndyH@discussions.microsoft.com> wrote: >I have a client who wants to create multiple email sub-domains (no problems >settiung that up) but then has requested that each sub-domain has it's own >catch-all mailbox for unresolved emails. > >Anyone have any pointers? > >Thanks > >...

Link two subforms in datasheet view (one sub-form, one sub-subform
I’d like to know if this is even possible. I have a form named “Frm Volunteer Opportunities” with a subform named “SubFrm Volunteer Opportunities”. The subform must remain in datasheet view and has fields like: [Event ID], [Event Date], [Start Time], [End Time], etc... I want to link another form (a sub-subform?) to the datasheet so that whenever my curser is located in one of the datasheet rows, this other form will present all volunteers scheduled for that event. Then, when you move the curser to the next row, those names would change. I’m guessing this means that the m...

Database Query #3
I posted this earlier with no replies. Anyone got any ideas? I am using a database query to retrieve data from another database excel file. The problem I am having is that the column of data which I have contains records which have both text and number formats. When the data transfers, it only brings those records which are number format. Is there any way to make is so that the database query will pull both number and text formats, as I can't easily change it so that it is one or the other? Thanks The following article in Dick Kusleika's web log may help: http://www.di...

Query based upon another query not returning decimal data
I have Qry2 based upon Qry1 and that query is based on a linked table in SQL 2005. Fld1 and Fld2 are in the table and defined as decimal(28,18) in SQL. Qry1 returns records containing Fld1 & Fld2 without any problems. Qry2 is a Crosstab query performing a Group By / Row Heading on Fld1 and a sum value on Fld2. Qry2 gets an error on Fld1 stating "Invalid Precision for decimal data type". If I use the CDec function on it, the error for this field does not appear any more - but then after some processing, I get this error on Fld2: "Invalid scale for decimal data type&...

Pivottables
Is it possible to use a pivottable to comapre 2 data sources and produce a pivottable of variances? Is so How? TIA Johnb This may work: I've copied|pasted one set of data under the other. Then add an additional column to serve as an indicator (old or new). Then created the pivottable with that indicator as a column field. Then I'd usually convert to values and add a few formulas to look for differences. Apply Data|filter|autofilter to find them. Another option that may not work. I've changed the values of the "new" data to negative put -1 in an empty cell and...

webquery function
Hi all, I am using since a while the webquery possibility. The thing I can't fix is the following: I use the macrorecorder to record my functions, to process the same later. My first step: start recording My second step" Data > External Data > New Web Query... search my webpage and enable the part I want to have in Excel and click Import until now nothing special My third step: In the Import Data screen, I click on the Properties button and disable the Save Query definition and click OK and OK again to get my data into Excel. Fourth step: stop the macrorecorder The result i...