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


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

Pivots - Auto calc % Sub total is of grand total
 

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


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=569364

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

Pivots - Auto calc % Sub total is of grand total
 

Thanks Mark for the warning.

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