+ Reply to Thread
Results 1 to 6 of 6

Pivots - Auto calc % Sub total is of grand total

  1. #1
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988

    Pivots - Auto calc % Sub total is of grand total

    Hi,

    Is it possible in a pivot to take the subtotal lines

    e.g Customer 1 total is £35.60 and work out what % this is of the Grand total. In this Example

    Customer 1 would be 9%
    Customer 2 would be 68%
    Customer 3 would be 23%

    Customer summary ¦Yr 2006
    Customer 1 Total ¦£35.60
    Customer 2 Total ¦£261.50
    Customer 3 Total ¦£87.60
    Grand Total ¦£384.70

    PS Trying to avoid paste special values and entering formulas manually

    VBA Noob

  2. #2
    Forum Contributor
    Join Date
    02-28-2006
    Posts
    690
    in this situation I would switch to a sum product solution - much easier in the long term - add up all the amounts for customer 1 and multiply by 100 then divide by all amounts for all customers - repeat for customer 2 etc.

    =100*(sumproduct((A1:A1000="customer1")*(B1:B1000))/sumproduct((b1:b1000)*1)))

    assumes amounts are in column B

  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Thanks Robert.

    I might go down that route, however i found this link which was helpful

    http://www.exceltip.com/st/PivotTabl..._Item/752.html

    I've change it slightly to look for % of total.

    VBA Noob

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Thanks Robert.

    I might go down that route, however i found this link which was helpful

    http://www.exceltip.com/st/PivotTabl..._Item/752.html

    I've change it slightly to look for % of total.

    VBA Noob

  5. #5
    MarkM
    Guest

    RE: Pivots - Auto calc % Sub total is of grand total

    Noob

    One thing you will want to consider is the number of rows in your data. I
    had this same problem a few months ago and I tried the sumproduct and it
    worked, it just was not practical for me in this instance. The problem was
    my data had 20,000+ rows and was growing monthly. I found it quicker to take
    10 minutes to manipulate my pivot table, copy paste the data into a new sheet
    then perform my % of subtotal calc. Rather than having my PC tied down for
    30-60 minutes while Excel did the sumproduct. I was fortunate in that the
    users of my report were more concerned about seeing this in a graph than in
    the report. So I just based my graph off this new range.

    "VBA Noob" wrote:

    >
    > Hi,
    >
    > Is it possible in a pivot to take the subtotal lines
    >
    > e.g Customer 1 total is £35.60 and work out what % this is of the Grand
    > total. In this Example
    >
    > Customer 1 would be 9%
    > Customer 2 would be 68%
    > Customer 3 would be 23%
    >
    > Customer summary ¦Yr 2006
    > Customer 1 Total ¦£35.60
    > Customer 2 Total ¦£261.50
    > Customer 3 Total ¦£87.60
    > Grand Total ¦£384.70
    >
    > PS Trying to avoid paste special values and entering formulas manually
    >
    > VBA Noob
    >
    >
    > --
    > VBA Noob
    > ------------------------------------------------------------------------
    > VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
    > View this thread: http://www.excelforum.com/showthread...hreadid=569364
    >
    >


  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Thanks Mark for the warning.

    VBA Noob

+ 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