Pivot Table Percentage of Grand Total Column

Here is my problem.  I am using Pivot Table to summarize financial data
to preform a margin analysis on it.  Currently I receive a dump of data
from the system which turn is put into the pivot table.  The way the
pivot table is currently displayed is as follows:

Sum of Amount	Account
Customer	  40000	            43000	     50000	         Grand Total
ABC	            $4,000.00 	    $(50.00)	     $(3,000.00)	$950.00
PDQ	           $6,000.00 	    $(350.00)	    $(4,000.00)	      $1,650.00

XYZ	            $5,000.00 	     $(500.00)	     $(3,500.00)
$1,000.00
Grand Total	 $15,000.00 	 $(900.00)	 $(10,500.00)	  $3,600.00

The 40000 and 43000 are the revenue, 50000 being the cost and Grand
Total is the Margin.  What I would like it to do is calculate a
percentage of the Grand Total / (40000+43000) and look something like
this:

Sum of Amount	Account
Customer	  40000	            43000	     50000	         Grand Total
Margin
ABC	            $4,000.00 	    $(50.00)	     $(3,000.00)	$950.00 	 24%
PDQ	           $6,000.00 	    $(350.00)	    $(4,000.00)	      $1,650.00
       29%
XYZ	            $5,000.00 	     $(500.00)	     $(3,500.00)
$1,000.00 	22%
Grand Total	 $15,000.00 	 $(900.00)	 $(10,500.00)	  $3,600.00 	   26%

Is there an easy way if at all way to do this with the pivot table.
Right now I have to write the formula outside the pivot able to just
reference the individual cells.  Problem with this is if the pivot
table changes after refreshing the data then I may lose that formula or
not cover all the new cells.  Help please..

0
appose (17)
2/8/2006 9:51:06 AM
excel 39879 articles. 2 followers. Follow

6 Replies
625 Views

Similar Articles

[PageSpeed] 38

To create pivot table in the layout you describe, it would appear that
the data has come out of a ledger and is probably in a format like...

Cust	Acc	$$
ABC	40000	4000
ABC	43000	-50
ABC	50000	-3000
PDQ	40000	6000
PDQ	43000	-350
PDQ	50000	-4000
XYZ	40000	5000
XYZ	43000	-500
XYZ	50000	-3500

Presumably the data is accessible somewhere so you can add some
'helper' columns beside the data.  Add some extra columns so you get
the data in the format..
Cust    Acc        $$	  Rev	  COGS
ABC     40000    4000    4000        0
ABC     43000	  -50      -50         0
ABC     50000   -3000        0   -3000
PDQ     40000	  6000   6000 	      0
PDQ     43000    -350     -350         0
PDQ     50000    -4000      0     -4000
XYZ     40000    5000     5000        0
XYZ     43000    -500     -500         0
XYZ     50000    -3500      0     -3500

The value in the column Rev is calculated using a formula..
=IF(LEFT(B2,1)="4",C2,0)  and COGS is calculated by replacing the "4"
with "5".

The Margin is the sum of all the values in the $$ column and the
revenue from the Rev column. Drag Rev, COGS and $$ into the data area
of the spreadsheet and then create a calculated field of $$/Rev and
express as a %.  If you need to have separate columns for Ac 40000 and
43000 then expand on the above.

Acc	(All)			
Data			
Cust     Rev     COGS	 $$       Marg%
ABC     3950    -3000   950     24.05%
PDQ     5650    -4000  1650    29.20%
XYZ     4500    -3500  1000    22.22%
Grand 14100   -10500  3600    25.53%
Total				

Hope this helps and would be delighted to hear that there is an easier
way!

regards..


-- 
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30757
View this thread: http://www.excelforum.com/showthread.php?threadid=509945

0
2/9/2006 9:46:42 AM
Steven,

Thanks for the response.  Your assumptions are correct that I am
getting the data out of the General Ledger.  What I have is a data dump
of a say months activities which includes all the invoices adjusting
Journal Entries to those invoices and the accounts associated with
them.  I then break the data out into four seperate pivot tables one
each of the four types of sales that we could have.  One invoice might
include three different types of sales categories.  This is usually
determined by the first three digits of the account code.  Because of
this it makes it difficult to preset the margins before putting the
data into a pivot table.  I don't know if live data would help with
this situation or not, but let me know I will see what I can do for
you.  Thanks for your help.

Jake

0
appose (17)
2/19/2006 7:22:27 PM
The situation you outline is what I like about pivot tables .. the
margin % calc is done dynamically so it does not matter what
products/categories etc that you filter down to because calculation is
still worked out correctly.  In the circumstance you detail you can not
calculate the percentages in the underlying data, it is only
'calculable' on the sum of the values in all the rows for a particular
product or product category as some rows will contain Revenue and some
COGS.

We use this approach for all financial reports so that a P&L, a Balance
Sheet or a sales/marging by customer or product/group are each just an
appropriately filtered and organised Pivot table based on the same set
of underlying GL transactions... usually directly connected to a view
of the GL transactions in the db.

Anyway .. I hope it helped


-- 
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30757
View this thread: http://www.excelforum.com/showthread.php?threadid=509945

0
2/19/2006 10:38:24 PM
Steven,

This works great, the problem that I am having is that I have about
twenty different revenue and COGS accounts.  Is there another way other
than Adding addtionional rows as I would like to stray away from having
twenty more rows.

Thanks,

Jacob

0
appose (17)
3/1/2006 12:19:13 PM
not sure I understand your issue.  

If you want to have less rows in the pivot table then you can create a
label that can be used in the pivot table which pre-summarises some of
the data and then use that label to group by.  (For example, put a
label which says "Rev" or "COGS" beside each sales and cost of sales
account)

Also, if the data is presently displayed like..

product category
product 1  100
product 2  100
product 3  100
------
300
------
you can 'double-click' the 'product category' heading and the
individual product lines will be 'undisplayed'

Some techniques for manipulating data are discussed in an (old!)
article at www.adaxa.com.au - use the search field to search for the
word 'pivot'.

regards...


-- 
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30757
View this thread: http://www.excelforum.com/showthread.php?threadid=509945

0
3/5/2006 10:41:23 PM
You could try throwing the $ volume into the pivot table twice.  Take
the second instance of the $ field and go into the field properties.
Select to see further options, then from the drop down chose % to
total.  Then you would see both $ and % to total.  It gives you a
percent for each account as well, but it is a quick solution to your
question.  You could hid ehte columns for any % to totals you don't
want to see.

0
kletcho (34)
3/8/2006 5:06:15 AM
Reply:

Similar Artilces:

Multi Table Queries
At my job, I use enter data through generated queries. However it appears that I can only ENTER data in a query when it is based on two tables. If it is three or more, it doesn't let me enter any info. Is this normal? Am I doing something wrong? -Pete Pete wrote: >At my job, I use enter data through generated queries. However it appears >that I can only ENTER data in a query when it is based on two tables. If it >is three or more, it doesn't let me enter any info. > >Is this normal? Am I doing something wrong? Yes, that's common. You should only edit data ...

Unique records in Pivot Tables
Excel 2000 I have data that stores Client Invitation to Tender and Supplier Contract details. Each Client ITT is uniquely identified. However we send out the same ITT to many suppliers so each request for service is given the same identifier, eg Reference Client/Supplier Contract Name Value Status AC/001 Client blah 4,000 pend AC/002 Client blah2 5,000 pend AS/001 Supplier blah3 3,000 pend AS/001 Supplier blah3 3,000 pend When I create my table by Client to get the total value pending ...

how can I get the total hrs of a delay when it is greater than 24
I work for an airline and sometimes I need to know the total hours of a delay, and when the delay is greater than 24 hours. In my report I hav something like this: arrival time in colum A departure time colum B result in colum C 10/23/09 21:00 10/24/09 23:00 2:00 and really the delay was of 26 hours. How can I obtain this? I already set up my Tools-Options-Canculation to 1904 date system Format Custom as [h]:mm No need to use 1904 date system -- Kind regards, Niek Otten Microsoft MVP - Excel "El Ixmahana" <ElIxmahana...

only the first 5 columns of a 10 column excel spreadsheet sort
How do I get the whole spread sheet to sort? There is a blue lox for the first 5 columns that limits the range of the sort. How do I remove it? Using Office 2003. Maybe if you remove the Data|list Select a cell in that blue box. Data|list|convert to range jrw562 wrote: > > How do I get the whole spread sheet to sort? There is a blue lox for the > first 5 columns that limits the range of the sort. How do I remove it? > Using Office 2003. -- Dave Peterson ...

Hide columns according to background fill color
I am having trouble understanding how Excel handles colors. I have a public sub that sets a public variable, "TermColor" using the RGB function. TermColor is of type MsoRGBType. In another module, I use the TermColor variable as follows: Sub WeedColsByColor(ByRef Clr, ByRef WS) Dim LastCol, i As Long With Worksheets(WS) LastCol = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count 'hide columns if they have one of the forbidden colors Debug.Print (CBool(.Cells(2, i).Interior.Color = Clr)) ...

Employee Master Table error after V10 SP3
i have updated V10 to SP3, from SP1, and all seems to be fine except if I try to access the employee master table. Either from cards>payroll>Employee, or payroll transaction entry. Anytime I need to do a lookup on an employee id. Here is the message: A Get Change operation on table UPR_MSTR failed accessing SQL Data If I go to the more information or details button: [Microsoft][SQL Native Client][SQLServer] Invalid column name 'EMPLSUFF' [Microsoft][SQL Native Client][SQLServer] Invalid column name 'Dex_Row_TS' -- Doug It looks like an upgrade script failed...

Pivot table novice
As a teacher we tried a new data analysis tool this year in my school which worked really well... except for the fact that it meant manually filling in a table. I'm sure there must be a more efficient electronic way of doing it, but I'm not really an expert on these things. I want to be able to put in a list of data: pupils names with scores from two consecutive tests. Then I want to produce a table with test 1 and test 2 as the headers, and pupils' initials appearing in the relevant box. I have managed to create a Pivot Table to show what I want, except of course, it total...

Can there be variable size columns in one report?
I want to create a report that has 3 sub-reports of different column widths. Is this possible? -The 1st sub-report has 1 column that occupies the entire width of the page -The 2nd sub-report can fit 2 columns in the page width -The 3rd sub-report can fit 3 columns in the page width Subreports can have any number of columns that don't have to be the same from one to another. Typically your columns should display across then down in order to render properly as a subreport. -- Duane Hookom Microsoft Access MVP "SheldonHinds" wrote: > I want to create a r...

Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and 1 from Table 2. All three entries are included for each day. Except somedays which may have only the one piece of data from Table 2 or none at all. How do I get the table to automatically fill in all the days whether they have information or not? =?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in news:A5EF53AB-6063-434F-BFC3-43259301751D@microsoft.com: > I have a pivot chart which pulls 2 pieces of information from one > table 1 and 1 from Table 2. All three entries are included for &...

Help to build a table
Please help me to build a table similar to this one The number on the first column will change after 16 times, it will go in sequence from 1001, 1002, etc… The number in the second column will change after 4 times and it will go from 1 to 4 The number in the 3rd column will go from 1 to 4 2nd and 3rd column will keep the same pattern. Cabinet Shelf Location 1001 1 1 1001 1 2 1001 1 3 1001 1 4 1001 2 1 1001 2 2 1001 2 3 1001 2 4 1001 3 1 1001 3 2 1001 3 3 1001 3 4 1001 4 1 1001 4 2 1001 4 3 1001 4 4 1002 1 1 1002 1 2 1002 1 3 1002 1 4 1002 2 1 1002 2 2 1002 2...

Pivot charts formatting
Hi, I want to change the formatting imposed by default in a pivot chart and I want that changed formatting to be retained permanently. For example, for my bar chart, for the different series I have selected colours / patterns but every time I refresh the pivot table / chart; excel falls back to its own colour coding. Any way to get around this? Many thanks in anticipation. Not directly. The only way to simulate this is to record a macro of the formatting changes that you make and then set it up to run whenever the PivotChart calculates. Assuming your pivot chart is on a seperate shee...

Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the columns by right clicking and choosing the desired options. In the tab that opens there is a slider which is supposed tho set the level of transparency of the column (selceted area). But so far i couldn't find a way how to use this slider. I know that there is an alternative way to get transparent bars by creating a rectangular object formating it and the use copy -> paste. But i wonder what is the slider for if you can't use it? Does anybody know have an idea? Cheers, Thomas ...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Combining Pivot Tables
Hi All, I have a data set of around 100,000 rows which I have imported into excel in two sheets (~50,000 rows each). The data is not in a format that excel can easily parse into a pivot table directly from the source - it requires some formulae in excel to be able to use a pivot table. I have used a pivot table on each of those sheets to summarise the data, and that works fine. However, I would like to be able to get a single summary pivot table from the two sheets (or from the two pivot tables). Is that possible, and if so, how do I go about it? Thanks, Alan. "Alan" <...

Unhandled database exception: A get/change operation on table 'Bat
During Edit Checks, we get the following message: Unhandled database exception: A get/change operation on table 'Batch_Headers' could not find a record. Then we get this message: This transaction was recovered during normal processing. You may continue processing this transaction. Any suggestions? You might want to run Check Links on payables. Before doing this, make sure you have a backup of your dynamics and company databases. Brenner -- www.KlenzmanConsulting.com "GPI" wrote: > During Edit Checks, we get the following message: > > Unhandled databas...

Column width
In Sheet 1 I have a certain amount of data, I want to select some cells and copy them to Sheet 2 keeping the same format. When I do this, the fonts and the colours remain unchanged, but the column width don't. I have tried paste special, but couldn't figure it out. Is this possible? Thanks in advance Regards, Emece.- --Copy range --Select the target cell and right click >PasteSpecial>All>OK --Keeping the target selection right click>PasteSpecial>select ColumnWidth>OK If this post helps click Yes --------------- Jacob Skaria "Emece"...

Grand Substraction instead of Grand Total in pivot table
Hello, I created a pivot table in Excel with Visual Report 2007. I'm using an Outline Code with 2 leaves : Receipts and Expenses. The pivot table created automatically a Grand Total so that $1000 Receipts and $800 Expenses => Grand Total = $1800. My accountant says $200. Generally, it's possible to create calculated fields in the Excel pivot tables. But in the pivot table created by the Visual Reprot, all the Calculated Field options are greyed out. Why ? Thanks for any help I don't know what visual report is, but suspect that it would be more a question for them not Excel a...

Conditional text field Total
Hi, In the detail section of a report I have a text box that based on a condition, it will be either a 0 or a 1. I want to total the control at the end of the report. I did this in another report a couple of years ago but it is not working now. In the previous report, the ControlSource of the total is = [TextBoxNameFromDetailSection] and it sums correctly. What am I doing wrong?? Thanks. Leah -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201001/1 Thanks but I see what was missing - "running sum over ...

Add values in a column according to value in another column
How can I add the values in a column according to values in another column? If there is any value in a row in column B, I want to include the value of the corresponding row in column A. I'm flexible as to whether this is ANY value (i.e. not empty) or greater than zero. Hi Paul Maybe something like this =IF(B1="","",IF(B1>0,B1+A1)) Regards Cimjet "Paul Kaye" <paulmjkaye@gmail.com> wrote in message news:05befaf3-9ba8-48c8-aebb-654f0269d1dc@34g2000hsf.googlegroups.com... > How can I add the values in a column according to values in another > colu...

How do I make a column be my default column in Access
I need to make my desricption field my default field. How do I do that? Right not it defaults to my items field. me.controlname.setfocus or in macro GoToControl "controlname" Bonnie http://www.dataplus-svc.com michelle wrote: >I need to make my desricption field my default field. How do I do that? Right >not it defaults to my items field. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1 Or, if you don't want to use events, simply set your tab order from the form design view. -- Frank H R...

Copying non-adjacent columns to adjacent rows
Hi all, I consider myself fluent in Excel, but I've developed a situation that has stumped me. Any help would be much appreciated. I might be able to solve this issue if somebody could show me how to add a number to a column. For example, if I want Excel to pull data from Column D, how can I get Excel to realize that column D is really the same thing as Column A + 3? I know you can use the column() command to get the numerical value for a column, but is there a way to have it do that in reverse, such that you could tell it the column number is 4 and it would know that you are referring...

Report to show totals for each day of month
Greetings, I have an Excel spreadsheet that I use to capture: Column A) Day of the month (1-May, 2-May, etc.) Column B) How many clients were in residence on each day (Occupancy) Column C) Total Capacity (27) Column D) Shows a "1" if the Occupancy is 90% or more of the Total Capacity, else "0" The Totals row shows: Column B) The average Occupancy for the month Column D) Totals the times there is a "1" in Column D Is there a way for me to capture this data in a Report? I've got a query set up that uses these expressions: For AdmitDate: <=[Dat...

counting dates <= 7 days ago based on criteria in a diff column
I have a spreadsheet that holds all tasks for a project. Column D holds a catagory and column Q holds the date closed. I need a formula (on a separate sheet) that counts all tasks of a specific category that were closed in the past 7 days. I already have a formula that calculates all tasks that were closed in the past 7 days, just need to add the additional criterion of the category. Hi, Try this =sumproduct((sheet1!D2:D30=A2)*((today()-sheet1!Q2:Q30)=7)) A2 on sheet2 has the specific category for which you want to count the closed tasks -- Regards, Ashish Mathur...

How do I clear a column of data without clearing the formulas?
I want to zero out a column of data, but not the subtotal formulas. How can I do that easily without going to every cell? Hi Select your column then choose edit / goto / special - constants - ok this will just select the non-formula cells then press the delete key cheers JulieD "EllenSwarts" <EllenSwarts@discussions.microsoft.com> wrote in message news:DD518142-47BC-438D-BE23-8DF1E9F72042@microsoft.com... >I want to zero out a column of data, but not the subtotal formulas. How >can > I do that easily without going to every cell? qlso look at: http://www...

excel command that counts conditions met in 2 columns?
is there a form of countif that increments only if conditions are met in two (or more) columns? e.g., countif(colA = 1 and colB = 2) Hi You need SUMPRODUCT. Have a look here for some guidance and post back if you need some more help. http://www.contextures.com/xlFunctions01.html#SumProduct Hope this helps. Andy. "brendalw" <brendalw@discussions.microsoft.com> wrote in message news:F21CD5F8-8679-4A8E-90FC-AA35657C19DC@microsoft.com... > is there a form of countif that increments only if conditions are met in > two > (or more) columns? e.g., countif(colA = 1 a...