Solved

how to calculate a percentage of a count in a Pivot table in Excel 2010

Posted on 2011-09-08
8
422 Views
Last Modified: 2012-05-12
On Sheet 1 I have a Table set up. It's called Table1. It has a list of parts that had inspections performed on them, and whether or not they passed or failed. On Sheet two, I have a pivot table set upt that looks at Table1 and displays a Count of Inspections grouped per month and a count of Failed Inspections.

It looks like this
 Pivot
So I want to know if I can add another field to the right of Failed Inspections (currently where I have dragged in another Count of P/F) that will tell me what percentage that is of the total number of inspections for that month?

Thanks in advance for the help!
0
Comment
Question by:SeyerIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36503035
You can include the field twice (2007 and later) and set the second field as "Show As Percentage of Total" in the field settings.

Thanks
Rob H
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36508303
I created a mock-up of what you have.  You have everything you need in the pivot table with Number of Inspections and Number of Failures to create the Failure % Ratio, and have it dynamically update when the PivotTable is refreshed.

See the image below.  Just select on a cell in the Number of Failures column (see active cell, below) - it can be anywhere in the Values section.

Then, above in the PivotTable Tools Ribbon, just select "Fields, Sets, & Items", then "Calculated Field"

You'll want to add a new field, ex., Inspections_Failed% with a formula like:

= IF(Inspection =0,0,'Num Failures' /Inspection)

Selecting Calculated Field Menu item from PivotTools Developer Ribbon
Creating Calculated Field to Show Ratio
See attached demo workbook.

Also, you might enjoy this link - more on Calculated Fields http://www.contextures.com/excel-pivot-table-calculated-field.html and Calculated Items http://www.contextures.com/excelpivottablecalculateditem.htm

Enjoy!

Dave
funWithCalculatedFields.xlsx
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36509981
This definitely looks like what I'm trying to do, however, I can't seem to get it to work on my spreadsheet.. I know my data is set up slightly differently but seems like that shouldn't matter.. I have a list of individual inspections and the pivot is doing a count of one of the columns (WO/LOT#) to determine how many inspections were completed and then it is counting the number of these that have an "f" in the P/F column to determine the count of failures.. I'm uploading a dummied down copy of my spreadsheet, perhaps you could take a look at it for me?

Thanks a bunch for the help Dave!
INSPECTION-LOG-2011.xlsm
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 36512722
Calculated Fields/Items don't work well on aggregates.  But we can help that with a couple helper columns in the source data.  I created two fields in your table that count the # of inspections (just an =1) and # of failures (=IF(Table1[[#This Row],[P/F]]="F",1,0)

Calculated Field            
1      cInspection_Failures%      =IF('HelpInsp#'=0,0,'Help Fails'/'HelpInsp#')
            
See attached, for an output like:
 Example output
Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36512728
Here's the attached file :)

Dave
INSPECTION-LOG-2011-r1.xlsm
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36512776
This looks great.. I will try to reproduce all of it here in a minute.. Just wrapping up another task really quick.. One quick question, How do you delete a calculated field from the pivot in it's entirety? Or modify it? i can't seem to figure it out..
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36512833
Go to the calculated field menu (PivotTools->etc.->Calculated Field).  There's a dropdown where you type in the name of the new field, just toggle to find an existing one, where you have the option to MODIFY or DELETE.

Dave
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36513021
Got it all set up! Thanks a bunch for the help!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question