Subtotal in a pivot table
 

I have a question on calculating something in a pivot table. What I am
trying to get is a % of the subtotal break in my table. I have a calculated
field but cannot figure out how to get it to calc. the % off the shop total
not the Total for the table.
See the example below. For Shop 246-Database I want the Sum of % of shop
total to be based off the Database DeliveryAmt (10575) / Shop 246 total
DeliveryAmt (17317) = 61%, not the pivot table total (185852) = 5.7%. Retail
should be Retail DeliveryAmt (6742) / Shop 246 total DeliveryAmt (17317) =
39%, not the pivot table total (185852) = 3.6%.

246 BRADENTON Database Sum of NContacts 88
Sum of DeliveryCust 6
Sum of DeliveryQty 8
Sum of DeliveryAmt 10575
Sum of % of Shop Total 5.69%
Grassroots Sum of NContacts 1
Sum of DeliveryCust 0
Sum of DeliveryQty 0
Sum of DeliveryAmt 0
Sum of % of Shop Total 0.00%
Retail Sum of NContacts 7
Sum of DeliveryCust 4
Sum of DeliveryQty 6
Sum of DeliveryAmt 6742
Sum of % of Shop Total 3.63%
246 BRADENTON Sum of NContacts 96
246 BRADENTON Sum of DeliveryCust 10
246 BRADENTON Sum of DeliveryQty 14
246 BRADENTON Sum of DeliveryAmt 17317
246 BRADENTON Sum of % of Shop Total 9.32%
Total Sum of NContacts 1046
Total Sum of DeliveryCust 98
Total Sum of DeliveryQty 147
Total Sum of DeliveryAmt 185852
Total Sum of % of Shop Total 100.00%


I know if I set up the pivot table to only show each shop individually it
will give me what I want. However, I have over 250 shops in total assigned
to different districts and I am trying to show the data for the each district
with the shop totals.

Any suggestions or advice is appreciated. Thanks

Subtotal in a pivot table
 

Not possible using built in but you can add formulas to the table like in
these examples

http://tinyurl.com/dfdqw

http://tinyurl.com/c6xkt


--

Regards,

Peo Sjoblom

"MarkM" wrote in message
...
I have a question on calculating something in a pivot table. What I am
trying to get is a % of the subtotal break in my table. I have a
calculated
field but cannot figure out how to get it to calc. the % off the shop
total
not the Total for the table.
See the example below. For Shop 246-Database I want the Sum of % of shop
total to be based off the Database DeliveryAmt (10575) / Shop 246 total
DeliveryAmt (17317) = 61%, not the pivot table total (185852) = 5.7%.
Retail
should be Retail DeliveryAmt (6742) / Shop 246 total DeliveryAmt (17317) =
39%, not the pivot table total (185852) = 3.6%.

246 BRADENTON Database Sum of NContacts 88
Sum of DeliveryCust 6
Sum of DeliveryQty 8
Sum of DeliveryAmt 10575
Sum of % of Shop Total 5.69%
Grassroots Sum of NContacts 1
Sum of DeliveryCust 0
Sum of DeliveryQty 0
Sum of DeliveryAmt 0
Sum of % of Shop Total 0.00%
Retail Sum of NContacts 7
Sum of DeliveryCust 4
Sum of DeliveryQty 6
Sum of DeliveryAmt 6742
Sum of % of Shop Total 3.63%
246 BRADENTON Sum of NContacts 96
246 BRADENTON Sum of DeliveryCust 10
246 BRADENTON Sum of DeliveryQty 14
246 BRADENTON Sum of DeliveryAmt 17317
246 BRADENTON Sum of % of Shop Total 9.32%
Total Sum of NContacts 1046
Total Sum of DeliveryCust 98
Total Sum of DeliveryQty 147
Total Sum of DeliveryAmt 185852
Total Sum of % of Shop Total 100.00%


I know if I set up the pivot table to only show each shop individually it
will give me what I want. However, I have over 250 shops in total
assigned
to different districts and I am trying to show the data for the each
district
with the shop totals.

Any suggestions or advice is appreciated. Thanks

Subtotal in a pivot table
 

Thanks Peo. I was wondering if thats what I had to do. Thanks for the links
with the formulas, it saved me a lot of time trying to figure this out on my
own.

"Peo Sjoblom" wrote:

Not possible using built in but you can add formulas to the table like in
these examples

http://tinyurl.com/dfdqw

http://tinyurl.com/c6xkt


--

Regards,

Peo Sjoblom

"MarkM" wrote in message
...
I have a question on calculating something in a pivot table. What I am
trying to get is a % of the subtotal break in my table. I have a
calculated
field but cannot figure out how to get it to calc. the % off the shop
total
not the Total for the table.
See the example below. For Shop 246-Database I want the Sum of % of shop
total to be based off the Database DeliveryAmt (10575) / Shop 246 total
DeliveryAmt (17317) = 61%, not the pivot table total (185852) = 5.7%.
Retail
should be Retail DeliveryAmt (6742) / Shop 246 total DeliveryAmt (17317) =
39%, not the pivot table total (185852) = 3.6%.

246 BRADENTON Database Sum of NContacts 88
Sum of DeliveryCust 6
Sum of DeliveryQty 8
Sum of DeliveryAmt 10575
Sum of % of Shop Total 5.69%
Grassroots Sum of NContacts 1
Sum of DeliveryCust 0
Sum of DeliveryQty 0
Sum of DeliveryAmt 0
Sum of % of Shop Total 0.00%
Retail Sum of NContacts 7
Sum of DeliveryCust 4
Sum of DeliveryQty 6
Sum of DeliveryAmt 6742
Sum of % of Shop Total 3.63%
246 BRADENTON Sum of NContacts 96
246 BRADENTON Sum of DeliveryCust 10
246 BRADENTON Sum of DeliveryQty 14
246 BRADENTON Sum of DeliveryAmt 17317
246 BRADENTON Sum of % of Shop Total 9.32%
Total Sum of NContacts 1046
Total Sum of DeliveryCust 98
Total Sum of DeliveryQty 147
Total Sum of DeliveryAmt 185852
Total Sum of % of Shop Total 100.00%


I know if I set up the pivot table to only show each shop individually it
will give me what I want. However, I have over 250 shops in total
assigned
to different districts and I am trying to show the data for the each
district
with the shop totals.

Any suggestions or advice is appreciated. Thanks