Percentage in a Pivot Table
Hi all!!
I feel like there should be an easy answer to my question, but I'm drawing a blank.
I have attached an example spreadsheet to help explain my question.
I am using a Pivot Table to count the number of times each of our employees uses a specific piece of equipment. We also have many areas that our employee's work in, so the pivot table not only separates out how much of which equipment is used by individual person, but also by which area that employee uses it in.
I am trying to get the pivot table to NOT count the number of times the individual person uses the specific piece of equipment, and instead give me a percentage.
The problem I run into, is when I try "value field settings" "Show values as" and then i can choose from "% of total", "% of column", % of row" etc. I find that all these do not give me the result i'm looking for.
What i'm looking for is to have the percentage of the equipment usage by individual person.
Example:
If it was just counting it would look like this:
Emp Equip1 Equip2
a 5 20
b 3 1
But I want it to give a percentage:
Emp Equip1 Equip2
a 20% 80%
b 75% 25%
Having the total that it is finding a percentage of, being the total pieces of equipment that individual person.
Thank you all so much in advance!!
I really think i made this more confusing than it actually is, but I think the example will explain it better than I can
Last edited by rmikulas; 09-24-2010 at 12:56 PM.
Hi All,
I haven't received a suggestions yet, so I was thinking that maybe I just made the more complicated then it actually is.
Please look at the Attached File called "Example" it really explains what is needed much more than reading my confusing explaination.
I look forward to any help I can get
Hi rmikulas,
Add two calculated fields, this way...
field %equip1:
=Equip1/(Equip1+Equip2)
field %equip2:
=Equip2/(Equip1+Equip2)
How?
Go to: Pivot Table tools>Options>Tools>Formulas>Calculated Field
See attachment
I hope it helps
John Franco
http://www.pivottabletutorial.net/
Re: Percentage in a Pivot Table
Hi jmffjff79,
Thank you.
Your example does help and did get the desired results for the "TOP LEVEL". That works perfectly for the TOTAL percentage usage for each employee.
On the example sheet pivot table, it has it sorted by 2 criteria. One is the Employee Number (which your example works perfectly for) and the 2nd is by the different areas that the employee works in.
Example:
Raw Data
Emp Area Eqp1 Eqp2
Tom A___ 2___ 1___
Tom B___ 3___ 2___
Sue B___ 1___ 0___
Sue A___ 2___ 1
Need the Pivot Table to say:
Emp Area Eqp1 Eqp2
Tom ____ 62% 38% (This part correct in your formula)
____ A __ 25% 12% (Need this)
____ B __ 38% 25% (Need this)
Sue _____ 75% 25% (This part correct in your formula)
____ A __ 25% 0% (Need this)
____ B __ 50% 25% (Need this)
I attached the Example back, that has your formulas you suggested, and you can see how it works for the total, just not the break down of the different areas.
Thank you so much for your help so far, I hope there is an answer out there for the rest.
Thanks again
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks