Pivot Table "% of" Question

rodrigo_solares

New Member
Joined
Mar 6, 2007
Messages
44
Hello to everybody,

I have a pivot table displaying the departments in the row section... and it's counting 2 things with 2 different fields; how many employees by department should be doing a course, and how many employees have already taken the course.

The real question is that i want a field inside the Pivot Table diplaying by row, what's the % of people who have taken the course.

Lets say that there should be 30 people in Finance in that course, but only 15 have taken it.. therefore the field should display 50% (15 of 30 in Finance department) instead of the % of the Total which sumarizes all departments.

Btw, i have tried to use the other "% of" options in the pivot table but i keep getting a N#A error.

Thanks in advance!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
876
Hi Rodrigo,

I think you need a calculated field.

I mocked up your data as

Dept Name Should Take Have Taken
Finance Dave 1 1
IT Dan 1 0
Admin Donna 0 0
Finance Denise 1 0
IT Darla 0 0
Admin Dwayne 1 1
Admin Dirk 0 0
IT Doris 1 1
IT Dennis 1 1
Finance Dilys 1 0

Then created a pivot table that looked somewhat like this:
Data
Dept Sum of Should Take Sum of Have Taken Sum of Pct
Admin 1 1 100%
Finance 3 1 33%
IT 3 2 67%
Grand Total 7 4 57%

where Pct is a calculated field based on this formula:
='Have Taken' /'Should Take'
 

rodrigo_solares

New Member
Joined
Mar 6, 2007
Messages
44
This was just what i needed,

Thanks a lot for the help!!!

It's weird how i need to go to the Pivot Table Menu to show the "Formulas Option", instead of just being in the right click menu.
 

rodrigo_solares

New Member
Joined
Mar 6, 2007
Messages
44
It works when I'm using sum for a field...

But if I'm using Count in the field, it doesn't work...
Book1
ABCD
1DEPTPILLTAKEN
2AXX
3BXX
4CX
5AX
6BXX
7CX
8AXX
9BXX
10CXX
Sheet1


This is exactly as my data is set up...

Any help here would be greatly appreciated
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
876
Since you know how it works for 1's and 0's (rather than X's) you could create two additional columns in your base dataset that show a 1 or a 0 depending on whether an X shows in the PILL or TAKEN columns.
I.e., let PILL_COUNT be in column D and TAKEN_COUNT be in column E.
The formula for PILL_COUNT in cell D2 would be =IF(B2="X", 1, 0) and similarly for TAKEN_COUNT the first formula would be =IF(C2="X", 1, 0). You can copy these formulas down their respective columns.

Now you can create the pivot table based on DEPT, PILL_COUNT, and TAKEN_COUNT (basing the PCT_TAKEN calculated field on the latter two fields.)
 

rodrigo_solares

New Member
Joined
Mar 6, 2007
Messages
44
Hello Dan,

Your solution seems great; however, I'm working on an Excel Sheet that is a database and cannot create any columns or alter the data.

Any ideas?
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
876
Are you building the pivot table in another workbook?
If so, then you could create another worksheet in that same workbook that would be the base data set with the two extra columns. Then run the pivot table off of that worksheet.
 

Forum statistics

Threads
1,181,418
Messages
5,929,793
Members
436,694
Latest member
dpatete

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top