Pivots - Auto calc % Sub total is of grand total

V

VBA Noob

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 Gran
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 Noo
 
R

robert111

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
 
M

MarkM

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