Pivot Table "% of" Question

rodrigo_solares

New Member
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.

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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'

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.

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

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.)

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?

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.

Replies
2
Views
292
Replies
3
Views
315
Replies
12
Views
472
Replies
7
Views
600
Replies
0
Views
632

1,218,540
Messages
6,143,098
Members
450,462
Latest member
PowerQueryKees

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?

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

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