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

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

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