Subtotal in a pivot table

I have a question on calculating something in a pivot table.  What I am 
trying to get is a % of the subtotal break in my table.  I have a calculated 
field but cannot figure out how to get it to calc. the % off the shop total 
not the Total for the table. 
See the example below.  For Shop 246-Database I want the Sum of % of shop 
total to be based off the Database DeliveryAmt (10575) / Shop 246 total 
DeliveryAmt (17317) = 61%, not the pivot table total (185852) = 5.7%.  Retail 
should be Retail DeliveryAmt (6742) / Shop 246 total DeliveryAmt (17317) = 
39%, not the pivot table total (185852) = 3.6%.

246 BRADENTON	Database  Sum of NContacts	88
 	 	Sum of DeliveryCust	                  6
 	 	Sum of DeliveryQty	                  8
 	 	Sum of DeliveryAmt	           10575
 	 	Sum of % of Shop Total        5.69%
 	Grassroots Sum of NContacts	                  1
 	 	Sum of DeliveryCust	                  0
 	 	Sum of DeliveryQty	                  0
 	 	Sum of DeliveryAmt	                  0
 	 	Sum of % of Shop Total        0.00%
 	Retail        Sum of NContacts	                  7
 	 	Sum of DeliveryCust	                  4
 	 	Sum of DeliveryQty	                  6
 	 	Sum of DeliveryAmt	             6742
 	 	Sum of % of Shop Total	3.63%
246 BRADENTON Sum of NContacts	 	96
246 BRADENTON Sum of DeliveryCust	 	10
246 BRADENTON Sum of DeliveryQty	 	14
246 BRADENTON Sum of DeliveryAmt	 	17317
246 BRADENTON Sum of % of Shop Total	 	9.32%
Total Sum of NContacts	 	1046
Total Sum of DeliveryCust	 	98
Total Sum of DeliveryQty	 	147
Total Sum of DeliveryAmt	 	185852
Total Sum of % of Shop Total	 	100.00%


I know if I set up the pivot table to only show each shop individually it 
will give me what I want.  However, I have over 250 shops in total assigned 
to different districts and I am trying to show the data for the each district 
with the shop totals.

Any suggestions or advice is appreciated.  Thanks

0
MarkM (21)
12/28/2005 5:56:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
875 Views

Similar Articles

[PageSpeed] 34

Not possible using built in but you can add formulas to the table like in
these examples

http://tinyurl.com/dfdqw

http://tinyurl.com/c6xkt


-- 

Regards,

Peo Sjoblom

"MarkM" <MarkM@discussions.microsoft.com> wrote in message
news:9F2DCDA3-A5E3-4DE3-9852-ADC84ECCA474@microsoft.com...
> I have a question on calculating something in a pivot table.  What I am
> trying to get is a % of the subtotal break in my table.  I have a
calculated
> field but cannot figure out how to get it to calc. the % off the shop
total
> not the Total for the table.
> See the example below.  For Shop 246-Database I want the Sum of % of shop
> total to be based off the Database DeliveryAmt (10575) / Shop 246 total
> DeliveryAmt (17317) = 61%, not the pivot table total (185852) = 5.7%.
Retail
> should be Retail DeliveryAmt (6742) / Shop 246 total DeliveryAmt (17317) =
> 39%, not the pivot table total (185852) = 3.6%.
>
> 246 BRADENTON Database  Sum of NContacts 88
>   Sum of DeliveryCust                   6
>   Sum of DeliveryQty                   8
>   Sum of DeliveryAmt            10575
>   Sum of % of Shop Total        5.69%
>   Grassroots Sum of NContacts                   1
>   Sum of DeliveryCust                   0
>   Sum of DeliveryQty                   0
>   Sum of DeliveryAmt                   0
>   Sum of % of Shop Total        0.00%
>   Retail        Sum of NContacts                   7
>   Sum of DeliveryCust                   4
>   Sum of DeliveryQty                   6
>   Sum of DeliveryAmt              6742
>   Sum of % of Shop Total 3.63%
> 246 BRADENTON Sum of NContacts 96
> 246 BRADENTON Sum of DeliveryCust 10
> 246 BRADENTON Sum of DeliveryQty 14
> 246 BRADENTON Sum of DeliveryAmt 17317
> 246 BRADENTON Sum of % of Shop Total 9.32%
> Total Sum of NContacts 1046
> Total Sum of DeliveryCust 98
> Total Sum of DeliveryQty 147
> Total Sum of DeliveryAmt 185852
> Total Sum of % of Shop Total 100.00%
>
>
> I know if I set up the pivot table to only show each shop individually it
> will give me what I want.  However, I have over 250 shops in total
assigned
> to different districts and I am trying to show the data for the each
district
> with the shop totals.
>
> Any suggestions or advice is appreciated.  Thanks
>


0
terre081 (3244)
12/28/2005 6:40:35 PM
Thanks Peo. I was wondering if that’s what I had to do.  Thanks for the links 
with the formulas, it saved me a lot of time trying to figure this out on my 
own.

"Peo Sjoblom" wrote:

> Not possible using built in but you can add formulas to the table like in
> these examples
> 
> http://tinyurl.com/dfdqw
> 
> http://tinyurl.com/c6xkt
> 
> 
> -- 
> 
> Regards,
> 
> Peo Sjoblom
> 
> "MarkM" <MarkM@discussions.microsoft.com> wrote in message
> news:9F2DCDA3-A5E3-4DE3-9852-ADC84ECCA474@microsoft.com...
> > I have a question on calculating something in a pivot table.  What I am
> > trying to get is a % of the subtotal break in my table.  I have a
> calculated
> > field but cannot figure out how to get it to calc. the % off the shop
> total
> > not the Total for the table.
> > See the example below.  For Shop 246-Database I want the Sum of % of shop
> > total to be based off the Database DeliveryAmt (10575) / Shop 246 total
> > DeliveryAmt (17317) = 61%, not the pivot table total (185852) = 5.7%.
> Retail
> > should be Retail DeliveryAmt (6742) / Shop 246 total DeliveryAmt (17317) =
> > 39%, not the pivot table total (185852) = 3.6%.
> >
> > 246 BRADENTON Database  Sum of NContacts 88
> >   Sum of DeliveryCust                   6
> >   Sum of DeliveryQty                   8
> >   Sum of DeliveryAmt            10575
> >   Sum of % of Shop Total        5.69%
> >   Grassroots Sum of NContacts                   1
> >   Sum of DeliveryCust                   0
> >   Sum of DeliveryQty                   0
> >   Sum of DeliveryAmt                   0
> >   Sum of % of Shop Total        0.00%
> >   Retail        Sum of NContacts                   7
> >   Sum of DeliveryCust                   4
> >   Sum of DeliveryQty                   6
> >   Sum of DeliveryAmt              6742
> >   Sum of % of Shop Total 3.63%
> > 246 BRADENTON Sum of NContacts 96
> > 246 BRADENTON Sum of DeliveryCust 10
> > 246 BRADENTON Sum of DeliveryQty 14
> > 246 BRADENTON Sum of DeliveryAmt 17317
> > 246 BRADENTON Sum of % of Shop Total 9.32%
> > Total Sum of NContacts 1046
> > Total Sum of DeliveryCust 98
> > Total Sum of DeliveryQty 147
> > Total Sum of DeliveryAmt 185852
> > Total Sum of % of Shop Total 100.00%
> >
> >
> > I know if I set up the pivot table to only show each shop individually it
> > will give me what I want.  However, I have over 250 shops in total
> assigned
> > to different districts and I am trying to show the data for the each
> district
> > with the shop totals.
> >
> > Any suggestions or advice is appreciated.  Thanks
> >
> 
> 
> 
0
MarkM (21)
12/29/2005 2:56:02 PM
Reply:

Similar Artilces:

Adding a calculated ROW to a pivot table
Does anybody know how to add a calculated ROW to a pivot table? I have a pivot table that is returning totals at the bottom, as it should, but I *also* need it to return that total as a percentage of grand total, directly beneath the total. I've always done this free-form in the cells below a pivot table before, but the size of this pivot is dynamic so that's not an option. Also--I'm using the pivot in Access, not Excel directly. Anybody have any tips? Thanks! ...

setting up tax tables in RMS
I have a client who has special tax needs. $0-$1600 is taxed at 9.25 $1601-$3200 is taxed at 9.75 $3201- and above is taxed at 7% so for example- for a $3,300.00 item- the tax would be $311.00. How would I set this up in RMS? Help! Thanks Andrea Andrea, Create 3 Sales Taxes. Sales Tax 1 Minimum Taxable Amount = None Maximum Taxable Amount = $1,600.99 Tax Rate = %9.25 Sales Tax 2 Minimum Taxable Amount = $1,601.00 Maximum Taxable Amount = $3,200.99 Tax Rate = %9.75 Sales Tax 3 Minimum Taxable Amount = $3,201.00 Maximum Taxable Amount = None Tax Rate = %7 Assign all of these taxes to one...

Table import Fixed Assets
Hello, Does anyone know why its not recommended to use Table Import to import Fixed Assets? Or what issues someone should be on the look out for when they use Table Import to import Fixed Assets? Thank You There is no reason I can think of. However, you will need to follow the same import guidelines outlined in the FA manual and follow the same order of importing data. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "Hazvi" wrote: > Hel...

Pivot Table Splits Columns for same entry #2
Thanks for the response. However, I seem to have misrepresented m problem because the solution doesn't help. In the Layout for the Pivo Table I have the following: In the "ROW": Item#, In the "COLUMN": Tota Lost $, Frequency of Reports generated by item #, In the "DATA": sum th counts of each item #. My chart displays the Item# on the x-axis, th sum the counts of each item # on the y-axis, the Total Lost $ and th Frequency of Reports generated by item # in the legend. There should only be one column per item # whether the chart type i stacked or clustered. I...

Incorrect subtotal when matching an invoice to a PO
Hello, I've recently had trouble when matching an invoice to a PO. It seems that each line item total was correct, but the subtotal was off by the landed cost I had added on to this shipment. I also had problems trying to match another, separate landed cost to this shipment. It seems like this just needs to be reconciled, but I'm not sure how. Please let me know if you have experienced this and what to do. Thanks! Lisa C. ...

Kits and Kit Components and SQL Tables
In the past few weeks I have been working on pulling data into an Excel Workbook from various GP Dynamics tables. The purpose of this workbook is to compile data to be used in a cash flow analysis. I have posted several questions on here and responders have been very helpful in answering my questions. I have a new one that hopefully someone can answer... I am pulling in sales line item data from tables SOP30300, SOP030200 as well as IV00101 (for item type). I am using IV00101 because some of our sales include items set up with an item type of "service" so the costs that ar...

Form creates unwanted rows in table
I've created an asset database which keeps track of computers, laptops etc. There is one company which has many departments and every department has many rooms. (surprise, surprise) There are four tables: tables Department and Asset and two "middle" tables DepartmentRoom and AssetRoom. The table structures are as follows: DEPARTMENT id name dep_type dep_num etc... ASSET id name a_type ip_num vendorID serial_num etc... DEPARTMENTROOM id name depID (foreign key to department) ASSETROOM id assetID (foreign key to asset) roomID (foreign key to depa...

html table import
Hi Our sales organization came up with the idea of taking a web based financial tool, write a huge Excel sheet around it, and update the data every week. From it, they get all kinds of stats and results for the management. So far so good. I'm in charge of the web application. Now since a few months, when they import, after exactly 139 lines, the formatting changes in excel. Instead of being right aligned, the table cells are suddenly left aligned (starting on column i), the 0.00 in the source is suddenly shown as such where it was 0 in the 138 lines before. Then after a few hundred...

Need to remove data in table due to input mask; cannot close.
ACCESS Table open, unable to close or modify telephone field. Had tried i/p mask of 0 due to "Unlisted" numbers appearing right-justified when doing a report. I sure thought I changed it back to a NO MASK prior saving the table. Now, table is open and I am unable to do anything with that field, even deleting the content, to satisfy the mask problem. Hands seems to be tied in 'catch 22'; I would have thought (!!??) that the table would not have saved properly if the input mask did not match the field contents. I'm stuck! Thank you so much. =?Utf-8?B?b2ZmZXJvY2t...

Pivot Table Defaults Turn Off Subtotal
Is there a way to change the default behavior of Pivot Tables? Specifically, can I change the default behavior for a Field to NOT show the subtotals? I know how to turn it off easy enough - it's just frustrating doing it over and over. Thoughts? I have not answer, just want to agree that this is a frustrating issue. Ie, everytime I build a Pivot Table, I have to go through the process of manually turning off the subtotals. Another issue I run into is that generally my pivot tables need to have the 'Field Settings' / 'Layout & Print' changed via a manual process ...

DAO and Table Locking
Greetings, I've written some code to create a relationship between table A and table B. The code looks something like this: Set rel = db.CreateRelation("A_B") rel.Table = "A" rel.ForeignTable = "B" Set fld = rel.CreateField("AID") fld.ForeignName = "BID" rel.Fields.Append fld db.Relations.Append rel This code works just fine except that a few lines later, I use DLookup() to find a row in table B, but I then get an error that table B is exclusively locked and cannot be accessed. Can anyone clarify why table B has been...

Table Indexes after upgrade to Dynamics GP v9
After upgrading to Dynamics GP we received an error that was due to "Index 'AK6GL00100' on table 'GMI.dbo.GL00100' does not exist". While troubleshooting this problem I found that there were not any clustered indexes on my GL00100 or GL00105 tables. So, I found the Indexes.cmp in the SQL\Install\0\Company on the server and tried to load the indexes for those tables from that file. That's when I discovered that the indexes in this file were not consistent with our account framework and assumed only 5 segments were in use. Thus, the reason why they failed to loa...

table of numbers 1 thru 52 pre sql2k5
Hi all, What's the easiest way to generate a single column of numbers in a table prior to sql 2k5? thanks, rodchar Check out: http://msdn.microsoft.com/en-us/library/aa175802(SQL.80).aspx -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "rodchar" <rodchar@discussions.microsoft.com> wrote in message news:C3DD455A-8DC5-42CD-884D-EB67EF6A4B5A@microsoft.com... Hi all, What's the easie...

copy record between tables
Hi, I have two tables with same structure. I need to copy a record from oneto other using vba codes. I'd like avoid to copy field to field.Thank you. Without knowing anything about your table structures, something similar to the following:Dim strSQL As StringstrSQL = "INSERT INTO tblCopy SELECT tblOriginal.* " _ & "FROM tblOriginal WHERE tblOriginal.KeyValue = 4"CurrentProject.Connection.Execute strSQL"Alberto" <saveas[at]iol.it> wrote in message news:uk2oUMBYHHA.3656@TK2MSFTNGP05.phx.gbl...> Hi,> I have two tables with same structure....

Exporting Ppivot table from Access to Excel
Hi there, (1) I am working with a huge Access database with 346,722 records (2) I created a pivot table in Access and that works (3) Now, I want to export this table to Excel (4) When I try to do this, it says "No Data" and returns a blank Excel page I understand that there is a limitation on the number of lines in Excel (~64K). But, since I have a massive database file, how can I possibly export the Pivot table to Excel? Any comments will be greatly appreciated! Thanks!!! rgds Kaushik You can build a series of queries based of the pivot that break the result down to manageab...

Help with pivot table
Hi, I am looking for a fix in below code, probably addition to it. I'm trying to export a query from ACCESS 2003 to EXCEL 2003. I do that each time with the export to excel button available in access which generates a temp file which I have to save all the time by naming it into appropriate excel file format. So far I have managed to export only the data sheet. I'm stuck with creating the pivot table portion in same code. These few lines I found while browsing and changed them w.r.t. my requirements. I would like to have a pivot table in sheet2 based on data ran...

SQL statement to find a particular column within all tables
I am looking for a query that will allow me to find all instances of a particular column within all tables so I know where they are all located. Does anyone have such a query they would be willing to share? Thank you. Pam, I posted this query a few months aback on my blog (http://dynamicsgpblogster.blogspot.com/2008/03/in-past-days-i-have-found-lot-of-people.html), but here is the excerpt: select distinct rtrim(objs.name) from syscolumns cols inner join sysobjects objs on (cols.id = objs.id) inner join sysindexes indx on (cols.id = indx.id) where (cols.name = 'ACTINDX') and (ob...

Can Pivot Table layout be copied???
A few weeks ago, I ended up with the perfect Pivot Table layout by dragging fields around -- I just don't remember how I got there! I have attempted to create a similar table (with new data) but, even though the field layout buttons are in the exact same locations, the table looks different. I'm trying to get "Sum of 2004", "Sum of 2005," etc. as column headings across the top instead of having all years one below the other. Is there any way to copy the layout from the "perfect" table to the new one? I answered my own question, with help from De...

How turn on Classic Pivot Table Settings
How does one turn on Classic Piviot Table Setting from Excel 2007. ...

sum items in a table based on description
I am interested in summing items is a table based on their description. Is there a formula that will add items together from column based on the contents in another cell reference in the same row? I know a pivot table will do this with some restrictions in the table design. You can use sumif if there is onbly one condition Assume you want to sum values in column C where column B is "x" =SUMIF(B2:B500,"x",C2:C500) Regards, Peo Sjoblom "Hrider" <hrider@yahoo.com> wrote in message news:ejggYE$MFHA.3328@TK2MSFTNGP14.phx.gbl... >I am interested ...

update data in table for new Format
I have a table with several thousand records. currently i have serial numbers stored as number but i now need to change the format. currently it is 4-8 digits. the new format should be xx-xxxxxx (2 digits a "-" and 6 more digits). I need this new value to store the "-" in the field. I know i can switch the field to a text field and this works but i need a way to convert existing data to the new format. I tried a mask format but it starts at the beinging. so if i put in a ##-###### and my data is 4021 it gives me 40-21 and i need 00-004021. Any ideas on this...

Median in pivot tables
The Excel pivot tables have a variety of functions. The one function that appears to be lacking is median. I'd sure like to be able to determine the median value grouped by some other variable. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micr...

Getting account out of subtotal
I am using Money to manage my finances, but i do not want my savings account to show up in my account subtotal. I'd like to be able to just have my credit accounts subtract from my checking without it looking at my savings. Is this possible, and if so how? The easiest way to do this is to create a report showing the accounts you want to use. -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny or http://money.mvps.org/faq for UK tips and fixes for MS Money. To send Microsoft your product wishes see http://...

when replicating, I get error message, but there is no table
when replicating with my assistant, I get that there are replication errors, but when I try to open the conflicts table, I get that there are "no conflict tables" =?Utf-8?B?YmttNGVhZ2xl?= <bkm4eagle@discussions.microsoft.com> wrote in news:5246CFE9-3BC1-4411-AB08-3E7B8D67D433@microsoft.com: > when replicating with my assistant, I get that there are > replication errors, but when I try to open the conflicts table, I > get that there are "no conflict tables" You'll have to look at the tables directly. The conflict tables are called BaseTabl...

Fast alternative to table based state transition matrix
I got this answer from comp.theory. It was completely obvious once it was explained. It is trivially simple to create a DFA based recognizer without a state transition matrix data table. Simply encode case statements corresponding to inputs within the case elements of a case statement corresponding to states. In at least some cases the (case within case) method might be faster depending upon whether or not memory is reduced enough to more than offset the higher case statement overhead to increase cache locality of reference. "Peter Olcott" <NoSpam@OCR4Screen.com...