Pivot Table Values as a % of a Subtotal

Hi!  I have a pivot table that is summarizing sales data by "facility type" 
and then "product category".  I want to present the data with each value 
shown as a % of the relevant subtotal (depending on which facility type and 
product category it happens to be in)...not of the column as a whole.  Is 
this possible?  If so, how?

Much appreciated!
2
Utf
1/26/2010 9:11:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1265 Views

Similar Articles

[PageSpeed] 27

Excel 2010 has this feature -- % of parent. You'll have to wait! :-(

"Little Mojo" <david.j.snyder@united.com> wrote in message 
news:06B1C68C-868C-4E6C-A6A8-B705358E4CB8@microsoft.com...
> Hi!  I have a pivot table that is summarizing sales data by "facility 
> type"
> and then "product category".  I want to present the data with each value
> shown as a % of the relevant subtotal (depending on which facility type 
> and
> product category it happens to be in)...not of the column as a whole.  Is
> this possible?  If so, how?
>
> Much appreciated! 

1
Bob
1/26/2010 9:18:29 PM
Reply:

Similar Artilces:

Opening Form Based on Value Input
I am wanting to open a form within a form based on the value input on a field on the main form. How do I accomplish this? I am a newbie to Access 2007. Thank you!!! Don't have 2007, but in 2003 you could change which form a subform control was displaying with <subform>.SourceObject = "XXXX" "Deb" wrote: > I am wanting to open a form within a form based on the value input on a field > on the main form. How do I accomplish this? I am a newbie to Access 2007. > > Thank you!!! ...

formatting pivot chart bar colour
Hello all, I am trying to create a timeline that shows cycle times of a machin in graphical form. At present I have the table set out as below, I then create a pivo chart showing the seperate processes stacked giving a total time. When the process number is the same (say load) I would like all part of the stack that are procees 1 to be the same colour, at the momen all the different processes show a different colour. I know you can change them individually but I may have hundreds o charts to do and would only be feasible if the colours were automati according to say the process number. ste...

Doubleclick i pivot tables
When I doubleclik in my pivot table, a new sheet is created, showing the details behind the cell content in the pivot table. When this new sheet is created, a kind of auto formatting is applied to the new sheet. My question is: How can I control this formating? Where does Excel get theese formats from? Some times I get quite weird results... I�m using Excel 2000. Hope anybody out there can help me. H�vard Dave Peterson answered your question a couple of days ago. You can see the thread in the Google archives: http://groups.google.com/groups?&threadm=3FB97939.94402802%40msn.com ...

Finding relationships between tables
All, How do I locate the relationships between tables in CRM. I am trying to relate the Incident table with the Incident Resolutions Activity table. Thank You, Barry You need to open the CRM SDK and look at the Reference-Shcemas section "Barry" wrote: > All, > How do I locate the relationships between tables in CRM. > I am trying to relate the Incident table with the > Incident Resolutions Activity table. > > Thank You, > Barry > In SQL Server Enteprise Manager you can also create diagrams to show links between the various tables -- John O'Donne...

Why am I getting a #VALUE! Error?
Previously I had this formula which was working fine: =IF(AND(OR($E$5=1,$E$5=2),$E$2="...3/12...",$E$3=" inch"),VLOOKUP($E$6,lookup!$C$3:$D$19,2,0),IF(AND($E$5=3,$E$2="...3/12...",$E$3=" inch"),VLOOKUP($E$6,lookup!$E$3:$F$19,2,0),"")) But after I add another part onto the end of the formula I am no getting a #VALUE! error (even though, if I enter information in th referenced cells, it seems to work fine and return the correc numbers): =IF(AND(OR($E$5=1,$E$5=2),$E$2="...3/12...",$E$3=" inch"),VLOOKUP($E$6,lookup!$C$3:$D$19...

Macro to copy subtotaled data
I have a spreadsheet that has been subtotaled. The formula in the subtotal cell is �=subtotal(3,j2:j61)� . Is there a way ,Using this information from the subtotal formula, which is the number of rows with each client's info. I would like to run a macro to copy the data from cells a2:q61 to a new worksheet named with contents of cell �I�. This process needs to repeat down thru aprox 6500 rows that have all been subtotaled down to 1500 separate lines . Try this code. The code copies rows so it doesn't care the number of columns. All it looks at is column A to get the Clie...

Converting a formula to a set value
I would like to export some figures that are calculations into another file, but have only the fixed values (i.e. eliminate the forumlas) when I export the data. How is this done? S. Rosenthal napsal(a): > I would like to export some figures that are calculations > into another file, but have only the fixed values (i.e. > eliminate the forumlas) when I export the data. How is > this done? Select cells, copy them and select the target. Use right mouse button and choose Paste special - Values. That's all. Marian Use Copy/Paste Special [Values]. David Hager Excel ...

Need to value dates
I do a lot of work in cleaning up mailing lists other people have generated. When I get any date column going, I usually =Value() it, to find out if any of the dates are bogus. Sometimes they are real dates, and sometimes they are "08/12/2006" and they get converted and formatted. I am confronted with a list which has the dates as "Aug 12, 2006" and I'm trying to figure how to get them as real dates. Any help would be gratefully appreciated. -- Regards, P D Sterling Dallas TX As long as they're all in the same format that you posted, you can use TTC (Text ...

Getting form expression results to update to table.
The totals from the expressions in a couple fields on the form don't update to the table. They (the expressions) work correctly in the form, but they (the results of the expressions) don't appear on the table after clicking Update All. Does anyone know how ot fix this? Hi Peg, First, forms don't contain fields. They contain controls (most commonly textboxes) which may or may not be bound to fields in the form's recordsource. If they are bound to a field, changing the data in the form will change the underlying field in a table (unless the form's recordsource ha...

Font Size Issues in linked tables
Hi. I have some tables in excel that I want to put into word and publisher documents. I encounter the same prob everytime... when the tables are linked in word or publisher.... the words/characters (ie. space between each character within words) seem to be unevenly spaced. As an excercise of interest I went into MS WORD... went INSERT/OBJECT/EXCEL WORKSHEET. Typed in some words in 8pt times new roman... clicked back into the word document. Same problem! Please help! Cheers! Hi Muppet The on-screen layout of Excel objects embedded in Word documents often don't behave in a truly w...

sql table not updated after converting from a97
Are there any known problems regarding SQL tables not updating and not leaving any error message either? We have an Access application that was written in Access 97 that contains VB code that worked fine. It's now been converted to Access 2000. The process runs and doesn't return any errors, but when the table is checked for the upates (indicated in reports generated during the process) the updates aren't there. Can someone please shed some light as to what we need to do to fix this? Thanks, Sharon ...

filter for missing values in a range
I have a list in Col A ( about 2000) one to four digit numbers. I would like to ( for example) , in the 700's be able to filter and find out which of the numbers from 700 to 799 is NOT in the list. I know how to copy and paste to another col, etc. Thanks in advance for the wonderful help all of you give. One play .. Assume your source data runs in A2 down In B2: =IF(ROWS($1:1)+700-1>799,"",IF(ISNUMBER(MATCH(ROWS($1:1)+700-1,A:A,0)),"",ROWS($1:1)+700-1)) Copy B2 down by 100 rows to cover the full spread of numbers to be checked, ie # of nums from s...

Advice on changing table indexes
Hello, Our Dynamics GP 10 database has been growing an growing. We've started archiving the data using Company Data Archive, but a big part of the problem still seems to stem from the built-in indexing on a couple of our largest tables: BM30400 - Item Serial/lot history for Inventory Assembly Rows: 92 Million, Data: 17.5 GB, Indexes: 34 GB IV30101 - Item Serial/lot history for Sales Rows: 45 Million, Data: 4 GB, Indexes: 7 GB Are there any DBA gurus out there who can suggest which of the default indexes are worth removing or re-configuring? I'm looking to improve the write ...

Pivot Chart Formats
Does anyone know a way to freeze pivot chart FORMAT, while allowing data updates? On Tue, 30 May 2006, in microsoft.public.excel.charting, John Francis <John@Francis.?.microsoft.com.invalid> said: >Does anyone know a way to freeze pivot chart FORMAT, while allowing data >updates? It's annoying the way the pivot chart destroys all your carefully chosen formatting every time it refreshes. Perhaps, just before you refresh, you could copy the formats to an empty sheet, then copy them back again onto the pivot table after the refresh? Perhaps the clever folk who know VBA cou...

Future Value of Increasing Payments
Does anyone have a formula that will figure the Future Value of an investment where the payments are steadily increasing each period? For Example: An investment offers an 8% annual yield over a 10 year period. I would like to pay $1000 the first year and then increase my payment by 3% each year to year 10. I know I can do it with a For loop, but is there a simple formula to use for this? Any help is greatly appreciated. Hi you have a post from Harlan in Excel.worksheetfunctions P.S.: please don't multipost :-) -- Regards Frank Kabel Frankfurt, Germany "Joe B." <Joe...

Pivot Table #25
When refreshing my Pivot Table, the sum function changes to count. Is there any way to lock the function on sum? You just clicked on the Refresh Data icon on the pivottable toolbar? (or rightclicked on the pivottable and chose refresh?) I've never seen excel do that when I do either of these (xl97, xl2k and xl2002). What version of xl are you using? (Maybe someone can verify it for you.) whtokbyu wrote: > > When refreshing my Pivot Table, the sum function changes to count. Is there > any way to lock the function on sum? -- Dave Peterson ec35720@msn.com ...

"Make Table" Issue
I have a "Make Table" Aggregate Query which is not producing the same data consistently. Are there any known issues doing this? Never had to before, but if I use a Function to see if "TableExists"...find it, and Delete that Table before running the "Make Table" again, maybe that will produce consistent results?? TIA - Bob On Thu, 31 May 2007 08:00:01 -0700, Bob Barnes <BobBarnes@discussions.microsoft.com> wrote: >I have a "Make Table" Aggregate Query which is not producing the same data >consistently. >Are there any known issues do...

Pivot table overwrites neighbouring cells #2
Hello, I have multiple pivot tables on the same worksheet to allow for easy comparison of data. The source data is based on the week and expected to be refreshed daily. As the week progresses the size of the pivot tables will grow and overwrite data below it. I am looking for a workaround to this problem or if there is a way to set the pivot table to insert a new row below it if the size will increase and before data is updated. Thanks in advance, Anissa Hi, There is no feature of pivot tables to do what you wish. You pretty much have two options that I can think of: 1. Prepare e...

Picking matching value from a list
Suppose I have a pair of columns giving nicknames and full names, e.g. John John Q. Public Bob Robert F. Kennedy Marty Martin Luther King Abe Abraham Lincoln Dick Richard M. Nixon Is there a worksheet function which will accept the value of a nickname from one cell along with the location of the above table, and return the name from the second column corresponding to the matching nickname in the first column? E.g. I type "Abe" and the function returns "Abraham Lincoln"? Seems like there should be, but I can't find it. - Rich Have a ...

Link Items in Same Table
Ok so I have a table which contains contract numbers and associated data records. How do I create a table and populate it to store link information between selected records in the above table? Table structure Ac-id - PK autonumber Account ID Field Ct-code number Conract Number Ac-title text Contract description Ca-code text customer address So what I want to do is record any commonality between 2 or more records in the above table. For instance it may be that 2 or more contracts occur on the same site. So I need a mechanism to store the contract "links" and a...

Auto-exclusion of data labels for zero values
Hi guys, xl97 question. Is there a way to have data labels enabled** for all points plotted but with the labels automatically *excluded* for any zero values ? The exclusion is desired for aesthetic reasons. **e.g.: via Data Labels > Show value in say, Format Data Series dialog I find that these "zero" values showing on say, a column chart, have to be individually selected and removed on each series in a chart, a pretty tedious task. The values plotted are derived through formula and there would always be a couple of points here and there evaluating to zero values from time-to...

Please Help: compare values in two columns present in seperate tab
Hi All, How can I compare values in two columns (columns are in different tables) ? I also want to display the values that dont match in a seperate table. For eg: If values in Column1 from Table1 does not match Values in Column1 from Table2 then display those values in a seperate table Is there a way to do this? THanks in advance Under query types, there is one for unmatched data and one for matched. -- Milton Purdy ACCESS State of Arkansas "sam" wrote: > Hi All, > > > How can I compare values in two columns (columns are in differe...

How do I change the category axis number formatting in a Pivot Ch.
I have created a Pivot Chart from Excel data (Excel 2000) and I need to figure out how to change the category axis number formatting. Currently, the dates in the chart are showing up in long form (01/01/2005) and I would like to change this to something shorter like 1/01. I changed the formatting of the data in the original raw data and then in the Pivot Table, but neither of these seemed to change the Pivot Chart formatting. When I right click on the category axis, I do not get the option the change the number formatting (I do for the value axis however). ...

Subtotaling
I have a sheet that has three fields that I want to run subtotals on: Division, Zone, and Rep. So far I've done subtotaling for each rep in the sheet, but now I need to do first a subtotal of the division, the zones within a division, and then the individuals in that zone. Is there a way that I can do it without having to remove the subtotal I've already done for reps. Reps are in the D column, Zones in F, and Division in E. Thanks Hi Start with the Division, then the Zones and then the Reps. Just go to Data / Subtotal each time, change the 'At each change in' to th...

Showing orders with a certain value of a bit field?
I've included a custom field on the order form. It's a bit field with the values YES and NO. The default value is NO. Our employees use this field to tell the system, if an order has been complete and is ready for invoicing. (We do not want to use the system possibility of completing an order). I've made a custom view of orders, where the view criteria is, that the value of this bit field is NO. And another view where the view criteria is, that the value is YES. However, when I select these views, I cannot see any orders. Could someone please explain, what I'm doing wr...