Pivot Table "% of" Question

[rodrigo_solares]

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!

 

[Dan Waterloo]

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'

 

[Datsmart]

For a nice Demo of setting up a calculated field in a Pivot table:
http://www.datapigtechnologies.com/flashfiles/pivot4.html
It should help you with your question.

 

[rodrigo_solares]

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]

It works when I'm using sum for a field...

But if I'm using Count in the field, it doesn't work...

Book1
	A	B	C	D
1	DEPT	PILL	TAKEN
2	A	X	X
3	B	X	X
4	C	X
5	A	X
6	B	X	X
7	C	X
8	A	X	X
9	B	X	X
10	C	X	X
Sheet1

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

Any help here would be greatly appreciated

 

[Dan Waterloo]

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]

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]

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.