+ Reply to Thread
Results 1 to 7 of 7

Pivot Table Percentage of Grand Total Column

  1. #1
    Jacob
    Guest

    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..


  2. #2
    Forum Contributor
    Join Date
    01-23-2006
    Posts
    194
    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..

  3. #3
    Jacob
    Guest

    Re: Pivot Table Percentage of Grand Total Column

    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


  4. #4
    Forum Contributor
    Join Date
    01-23-2006
    Posts
    194
    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

  5. #5
    Jacob
    Guest

    Re: Pivot Table Percentage of Grand Total Column

    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


  6. #6
    Forum Contributor
    Join Date
    01-23-2006
    Posts
    194
    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...

  7. #7

    Re: Pivot Table Percentage of Grand Total Column

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1