+ Reply to Thread
Results 1 to 10 of 10

Pivot table column result - total percentage

  1. #1
    Registered User
    Join Date
    07-17-2021
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    77

    Pivot table column result - total percentage

    In my pivot table on the "Rep Level" tab, I need my E column on my pivot table to show an overall percentage of the total sum of exceptions (column D) divided by the total sum of estimates (column C) for each person. Example: cell E4 needs to show the percentage of D4/C4. The answer should should come to 19.52%. What value field settings or pivot table setup do I need to change to get it to calculate the right result?
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Pivot table column result - total percentage

    I don't like Pivot Tables (I can't get calculated fields to do what I want!!), so I'm trying to learn how to use more advanced Excel formulae to do what I want.

    You maight not like this... or want to use it... but take a look, it seems to do what you wanted.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Adjust the bit in red, if needed.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    07-17-2021
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    77

    Re: Pivot table column result - total percentage

    The sole reason I was using the pivot table is new information will be added throughout the year on the Reinspections tab with new names that aren't already in my list and a simple refresh of the pivot table will bring the new data into the calculations.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Pivot table column result - total percentage

    No need for refresh with a formula!

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,436

    Re: Pivot table column result - total percentage

    Add calculate field to table

    1419662.png
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: Pivot table column result - total percentage

    That sounds like a simple calculated field dividing one by the other to me - see attached.

    Note to self: refresh before posting!
    Attached Files Attached Files
    Rory

  7. #7
    Registered User
    Join Date
    07-17-2021
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    77

    Re: Pivot table column result - total percentage

    I would love to know how you added that to the pivot table with the formula needed!

  8. #8
    Registered User
    Join Date
    07-17-2021
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    77

    Re: Pivot table column result - total percentage

    I think I found it in the PivotTable Analyze tab.

  9. #9
    Registered User
    Join Date
    07-17-2021
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    77

    Re: Pivot table column result - total percentage

    Thank you this was the answer I was looking for!! Any chance you know how I can get the pivot table to automatically refresh when new data is entered on the Reinspections tab? I turned on the feature to automatically refresh when the file is open, but is there another option that would make it instant once new data is added?

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: Pivot table column result - total percentage

    You would need code for that. I often use a Worksheet_Activate event in any sheet with pivot tables on it so that they refresh when I switch to that sheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 11-14-2021, 04:12 PM
  2. Comparing a percentage of a total to the total in pivot table and chart
    By BillDoyle in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-27-2018, 03:56 PM
  3. Percentage of Total calculation in Pivot Table
    By godonnell06 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-06-2016, 02:39 PM
  4. [SOLVED] A Second Column Which Shows Percentage Of Total In The Pivot Table
    By zanshin777 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-23-2015, 08:17 AM
  5. Pivot table - how to show percentage of total?
    By GoldbergJesse in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-19-2015, 03:31 PM
  6. Replies: 0
    Last Post: 10-15-2012, 06:09 PM
  7. [SOLVED] Pivot Table Percentage of Grand Total Column
    By Jacob in forum Excel General
    Replies: 6
    Last Post: 03-08-2006, 01:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1