% of subtotal in pivot Table

We have several complex pivot tables and are trying to get a % of the 
subtotal instead of the total.  We have infomation by state (the subtotal) 
and want to know the % of dollars by state not the % of each bucket of total 
of all the data.  If this possible.  We have tried calculated fields and 
nothing seems to work.  Thanks for any ideas.
0
Utf
7/12/2005 7:21:03 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
354 Views

Similar Articles

[PageSpeed] 49

There's no option to calculate the percent of a subtotal in a pivot 
table. You could do the calculation outside of the PivotTable.

Or, create a second pivot table, based on the first one, and use the % 
Of Column calculation to show the state percentages. There's an example 
here:

   http://www.contextures.com/xlPivot10.html

Tiffany C. wrote:
> We have several complex pivot tables and are trying to get a % of the 
> subtotal instead of the total.  We have infomation by state (the subtotal) 
> and want to know the % of dollars by state not the % of each bucket of total 
> of all the data.  If this possible.  We have tried calculated fields and 
> nothing seems to work.  Thanks for any ideas.


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

0
dsd1 (5911)
7/13/2005 12:27:22 AM
Thanks Debra.  I will try the second pivot table option.  I do wish they 
could throw this in the next time they are updating, we would get a lot of 
use out of it in my office.

Thanks again for your thought.

"Debra Dalgleish" wrote:

> There's no option to calculate the percent of a subtotal in a pivot 
> table. You could do the calculation outside of the PivotTable.
> 
> Or, create a second pivot table, based on the first one, and use the % 
> Of Column calculation to show the state percentages. There's an example 
> here:
> 
>    http://www.contextures.com/xlPivot10.html
> 
> Tiffany C. wrote:
> > We have several complex pivot tables and are trying to get a % of the 
> > subtotal instead of the total.  We have infomation by state (the subtotal) 
> > and want to know the % of dollars by state not the % of each bucket of total 
> > of all the data.  If this possible.  We have tried calculated fields and 
> > nothing seems to work.  Thanks for any ideas.
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
TiffanyC (1)
7/14/2005 1:17:05 PM
You're welcome. You can send suggestions to:

          mswish@microsoft.com

Put "Excel" in the subject line, so your suggestion will be forwarded to 
the Excel product managers.


Tiffany C. wrote:
> Thanks Debra.  I will try the second pivot table option.  I do wish they 
> could throw this in the next time they are updating, we would get a lot of 
> use out of it in my office.
> 
> Thanks again for your thought.
> 
> "Debra Dalgleish" wrote:
> 
> 
>>There's no option to calculate the percent of a subtotal in a pivot 
>>table. You could do the calculation outside of the PivotTable.
>>
>>Or, create a second pivot table, based on the first one, and use the % 
>>Of Column calculation to show the state percentages. There's an example 
>>here:
>>
>>   http://www.contextures.com/xlPivot10.html
>>
>>Tiffany C. wrote:
>>
>>>We have several complex pivot tables and are trying to get a % of the 
>>>subtotal instead of the total.  We have infomation by state (the subtotal) 
>>>and want to know the % of dollars by state not the % of each bucket of total 
>>>of all the data.  If this possible.  We have tried calculated fields and 
>>>nothing seems to work.  Thanks for any ideas.


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

0
dsd1 (5911)
7/14/2005 10:43:29 PM
Reply:

Similar Artilces:

Error: Can't clean up the following tables: PriceLevel. 05-31-05
I cannot start CRM application. In error log ther is message Error: Can't clean up the following tables: PriceLevel. I tried to delete records with deletionstatecode = 2 in SQL and restarted SQL and CRM services bun nothing helps. Can someone help me? Thanks. Normally you don't have to directly interact with CRM Databases : it's not supported and can act on the MS CRM good operation. "Mikkhail" wrote: > I cannot start CRM application. In error log ther is message Error: Can't > clean up the following tables: PriceLevel. I tried to delete records with &...

lookup tables
ok, i have a value, 15.086, which i want to look up in a table of values which are all integers, shown below. 10 12 18 22 33 Mathematically, the closest value is 18. However, when i use VLOOKUP it tells me that 12 is the closest, because it searches down the list, and not up. Is there a way of searching up the list, or both up and down, in order to find the true closest value? Regards, Phil Newman A bit long winded but =IF(ISNA(VLOOKUP(A3,$E$1:$E$5,1,0)),INDEX($E$1:$E$5,MATCH(A3,$E$1:$E$5,1)+1),VLOOKUP(A3,$E$1:$E$5,1,0)) The value to lookup (15.086) is in A1 and the table of values i...

Pivot Charts
I have created Pie charts from the pivot tables in Excel and have selected my own colours on the pie charts. However, when I select Refresh Data from the pivot table menu, I loose all my formatting. This is VERY annoying but perhaps I have a certain option set and that's why this happens. Anyone know how to keep the formatting of the charts from being reset when a refresh is done? Thanks in advance As posted previously. This is a known bug that required vba code to re-apply formatting. XL2000: Changing a PivotChart Removes Series Formatting http://support.microsoft.com...

Pivot Tables...
I'm looking to learn Pivot Tables. Can anyone out there let me know any good texts on this? Thank you in advance. Pivot Table Data Crunching by Jelen & Alexander; quepublishing ISBN 0-1897-3435-4 web tutorials at: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/ best wishes -- Bernard V Liengme Microsoft Excel MVP w...

Show an empty query field when there is no underlying table data
I have two tables containing information on stem cell transplants and the therapies that patients have received related to that transplant. The tables, somewhat simplified, look like: tbl_transplant: idper namesur namegive datebmt tbl_therapy: idther idper type name There are 27 unique types of therapy. I want to extract a single row of data per transplant in a query to show two particular therapies, CON and GVH. In my query below I have included the therapy table twice, once to pull out CON and the other to pull out GVH. If there is no CON or GVH record matching the ...

Lookup Table
i am trying to write a formula so that i can do the following from the table WORKING PRESSURE BY ANSI CLASS (psig) Temp (°F) 150 300 600 900 1500 2500 -20 285 740 1480 2220 3705 6170 100 285 740 1480 2220 3705 6170 200 260 680 1360 2035 3395 5655 300 230 655 1310 1965 3270 5450 400 200 635 1265 1900 3170 5280 500 170 605 1205 1810 3015 5025 600 140 570 1135 1705 2840 4730 650 125 550 1100 1650 2745 4575 700 110 530 1060 1590 2655 4425 750 95 505 1015 1520 2535 4230 800 80 410 825 1235 2055 3430 850 65 320 640 955 1595 26...

Pivot Chart Dates not in correct order
I have a pivot table that has a week ending column and the date of 7/10/09 is coming up before 7/3/09. Can you tell me how to get this in the correct order? Right-click the field button, advanced, sort ascending. Alterntively, you may have text that looks like dates, instead of true dates (stored as numbers), in which case you would need to modify your source data. (DATEVALUE function will help) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ls" wrote: > I have a pivot table that has a week ending column and the date of 7/10/09 is...

Subtotals in Pivot Tables
I've got a spreadsheet with a number of columns and I could do with putting a subtotal into a column in the middle of the data. Currently the columns are: Charged - Location - Size - Item Every line in my source data has a value in the Size column which is the same for each Location so that if I delete a line I don't end up without a Size for a Location.... but I want to be able to total the Size for each Charged and currently all I can do is get it to count every Size for every Item which gives me the wrong answer. So for instance if I've got data of: Charged - Customer A ...

New Tables & Queries Force me to Save As
I just took over a DB from the former developer. I go to "Create query in Design View" or "Create table in Design View". Do anything or nothing. I try to close. It prompts me for a Save As, and won't let me close until I give it a name and finish the Save As. PITA. I can't build a temp query without having to save it. Then I have to go and delete it. And if the query has a syntax error, the Save As fails. I have to make the temp query valid, do the Save, then delete. PITA. Does anybody know what setting she changed that is forcing a Save As? Thanks, Chris Ar...

Pivot Chart
Hi there, I have created a pivot chart (stacked column) in which I also use a secondary axis represented as a line. My problem is this. Whenever I change my data or criteria (via the pivot table drop downs)my secondary axis disappears from the chart. I am very new to the whole pivot chart thing so am I missing something simple here? Help! Thanks, Deb This is a known problem pivot chart formatting. There's some info in the following MSKB article, which suggests recording a macro as you apply the formatting: XL2000: Changing a PivotChart Removes Series Formatting http:/...

How do I backup/restore specific SQL tables?
I would like to backup and restore specific GP tables in SQL. What is the best way to do this? Is the best way to use the Import and Export utility in SQL? I would like to backup specific POP tables and be able to restore the POP tables if something goes wrong while running some other scripts to fix an issue. So I was planning to backup (export) the specific POP table to a CSV flat file using DTS in SQL. Then if need be, restore (import) the POP data in the CSV flat file back into the original table. Would this work? When doing the backup (export) should I only backup the specific dam...

Pivot Table #27
Dave, I want to do this at the same time. How to do this?Can you please help me? Regards. -- sganti ------------------------------------------------------------------------ sganti's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15170 View this thread: http://www.excelforum.com/showthread.php?threadid=268003 What happened when you used the extra column with the formula in it? sganti wrote: > > Dave, > I want to do this at the same time. > How to do this?Can you please help me? > Regards. > > -- > sganti > ----------------------...

Repeat labels in Pivot Tables
Is there a way to get excel to repeat the label on a pivot table so that there are no blank fields on the report? No... Apparently in XL2010 that is a new feature but at this point... No -- HTH... Jim Thomlinson "shawkins84" wrote: > Is there a way to get excel to repeat the label on a pivot table so that > there are no blank fields on the report? > Not and keep it a pivottable. If I don't need the pivottable, I'll convert to values (select the entire PT range and edit|copy, then edit|Paste special|values). If I need the pivottable, t...

Add a cumulative count to a pivot table
could some one refer me to a resorce where i could see how to add a cumulative count column to my pivot table... Regards, ...

Pivot Table Reversing
I have a Pivot Table that someone has created for me, which lists some data chronologically by month which has the earliest months at the left, and latest at the right. Is there any way I can reverse the order so that the latest data is at the right? I am fairly Excel conversant, although I have never dablled with Pivot Tables before, also, I know some VBA so a solution along those lines wouldn't bother me either. You could try a descending sort on the pivot table field concerned. "Neil" <Neil@discussions.microsoft.com> wrote in message news:C01E2FB1-6B0C-41E1-BC2E...

Move data from Primary table to a One to Many Table
I have a DB which has contact info stored in a One-to-Many table. The main Table has some additional contact info which I would like stored in the One-to-Many table. Data is seperated by fields such as Name, HomePhone and WorkPhone. These fields exist in the One-to-Many table as well. Is there a way to run a Query that would accomplish this? If so how? Many thanks in advance. "Mary M" <none@no_email.com> wrote in message news:eD76N4ykHHA.4876@TK2MSFTNGP03.phx.gbl... >I have a DB which has contact info stored in a One-to-Many table. The main >Table has some addi...

Football League Table Spreadsheet #3
Barbara, I have now managed to sort it out...I had to enable macros. It is exactly what I was looking for and I now have it up and running. Many thanks for all your help. Kind regards...Gracust : -- gracus ----------------------------------------------------------------------- gracust's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27395 ...

Excel table from access sorting problem
I created an Excel97 table by transfering data from an Access97 table successfully. I could sort the new table but when I add new records they will not sort into the original excel table of access data. What am I doing wrong? Jadd, The most likely thing is that you have text from the Access table and numbers you're adding. They sort differently. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jadd" <Jadd777@JUNO.com> wrote in message news:1cb3c01c38879$fd639c90$a601280a@phx.gbl... > I created an Excel97 t...

Subtotal Problem
I have imported data from a golf software program listing three rounds of golf for individuals. I want to subtotal their scores based upon a team within the tournament. When I try the subtotal command Excel separates the team members but returns "0" for the subtotal. I was told that Excel does not recognize the content of the cells and I must multiply their value by "1" and do a special paste command . . . I forgot how to do that. Also, is this a problem with the golf software, data transfer, or something else? Thanks, Tom I don't know about golf software, but w...

Create Table Auto Answer
I have a query that updates an exisiting table. When i run the query it prompts "You are about to run a make table query that will modify data in your table" To which you have to answer Yes or No It then prompts "The existing table will be deleted before you run the query" Yes, No Is there anyway to have access automatically answer those questions when running the query through a button on a form? Thanks James Before you run the query put in this line DoCmd.SetWarnings False after the last line DoCmd.SetWarnings True Allan "James" <fake@em...

Copying subtotals in Excel 2000
When I try and copy subtotals to a second sheet all rows are displayed. How can I copy just the subtotals? -- pearsall Display only the subtotal rows Select the entire range Press the F5 key and click on Special Check 'Visible cells only' Click on OK Copy Go to your other sheet Edit>Paste Special> Values "pearsall" wrote: > When I try and copy subtotals to a second sheet all rows are displayed. How > can I copy just the subtotals? > -- > pearsall When copying subtotals, Edit>Paste Special> Values isn't necessary Using Edit>Paste is qui...

Pivot Table #15
At various times (for reasons I can't explain) my pivot table data defaults to a 'count' rather than a 'sum' of my data. This is annoying. Can I make it always default to 'sum'? You can't change the default settings for the data fields. If a field contains blank cells, or cells with text, it will default to COUNT. Otherwise, it will SUM. Jonathan Cooper wrote: > At various times (for reasons I can't explain) my pivot table data defaults to a 'count' rather than a 'sum' of my data. This is annoying. Can I make it always default to ...

Table cell background not showing
I made a html-page with microsoft Frontpage to use as an Outlook template. I saved it in Outlook as an .oft-file and looks good when I compose a new message. However, the receiver of the message will not see the background I have in one of the table cells. I don't know if this has anything to do with this problem but I discovered that you cannot have a background in an MS Word table cell. Any help will be appreciated. >-----Original Message----- >I made a html-page with microsoft Frontpage to use as an Outlook >template. I saved it in Outlook as an .oft-file and looks good when ...

pivot table issue
please hel column a = range of dates or code word column b = codes (often repeating in reference to different dates & codes in column a column c = qt i make a pivot table wher column b becomes the row valu column a becomes the column valu & column c becomes the data valu the problem is that the resulting pivot table puts items with the same column b value but differend column a values on seperate lines rather than putting them on the same line with different row values in the data field any help would be appreciated wish i could send an attachment here --- would obviously be easier ...

ODBC data source Table error
Sorry for the duplicate post but I am desperate and am trying a more active forum. Original post: I have a Visio drawing with one shape that is linked to an Excel spreadsheet. If I right click and select "Refresh Shape Properties" it will refresh correctly. However, if I deselect the shape and right click on the drawing and select "Refresh Linked Shapes" I get an error response "the table "VisioTransfer" specified in the selected shape does not exist in the ODBC data source. The only data source file assigned does have the table "VisioTransfer" ...