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

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" <[email protected]> wrote in message
news:[email protected]...
> 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 that’s 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>