Pivot table calculation - % of different field subtotal

[P0lar]

I have been handed an excel sheet to fix which includes a pivot table:

Location Time spent Time paid
--Site A 1000 2000
----Lunch 100 150
----Break 200 150
----Work 700 1700

--Site B
----etc.

I need to add in a column which calculates time spent / site total time paid, for example Lunch in the above would be 100 / 2000, Break would be 200 / 2000 etc. The number of sites and the work types in each can vary and changes if the table data is refreshed. I've used "show values as..." but this only seems to get me a percentage of the parent total in the same field. Ideally I'd like to avoid VB if possible.

 

[chirayu]

Add a calculated field with a manual value of 2000 e.g. formula will be something like =LUNCH/2000. sample file attached

 

[P0lar]

Thanks Chirayu,

Unfortunately the total isn;t always going to be 2000, I need it to recalculate as the total paid time in each site changes, or the number of rows of data I'm pivoting change.

If I could add a calculated field of [LUNCH; WORKED] / sum[SITE; PAID] that would work.

 

[P0lar]

I think I've solved it using a couple of helper calcs, not sure if it can be done in the pivot table directly - here's what I've got to

 

[chirayu]

Oh so that's what you wanted. In that case just do a simple formula table. Sample file attached. I've made 2 different types of table so use whichever you like for your actual file.