1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table calculation - % of different field subtotal

Discussion in 'Ask an Excel Question' started by P0lar, Jan 11, 2018.

  1. P0lar

    P0lar Member

    Messages:
    34
    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.
  2. chirayu

    chirayu Well-Known Member

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

    Attached Files:

  3. P0lar

    P0lar Member

    Messages:
    34
    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.
  4. P0lar

    P0lar Member

    Messages:
    34
    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

    Attached Files:

  5. chirayu

    chirayu Well-Known Member

    Messages:
    786
    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.

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page