Pivot Table Question

PraveshG81

New member
Joined
Oct 6, 2011
Messages
22
Reaction score
0
Points
0
Hello,

I was hoping someone could help me build a pivot table that would tell me how much headcount we have in a Practice Group based on an applicant's application status. So, for example, in the Raw Data tab Person 1 wants to be in two practice groups, so he would be a .5 headcount in one group and .5 headcount in a second group. Person 2 wants to be only in one group so he is 1 headcount in that practice group.

What I am currently doing in the School_Practice Group tab is creating one pivot table to do a head count for practice group preference 1 and a second pivot table for practice group preference 2 and then adding those value together on a 3rd calculated table.

Is there anyway to do this in just one pivot table? Right now this is simple but if slice this data by say: Practice Group by Law Schoo (vertical) and current status (horizontal) this will get very difficult.

Thanks for your help!
 

Attachments

  • Pivot Table Quetion.xlsx
    24.3 KB · Views: 31

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Could you just extend the pivot table source to include Raw Data column H, with a column heading, then you could pivot the total.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
You could just add a Calculated field with a formula of =PG1+PG2, and use that field alone
 

PraveshG81

New member
Joined
Oct 6, 2011
Messages
22
Reaction score
0
Points
0
Hi Bob,

Unfortunately I can't do this, the biggest problem is the Practice Group Preference 1 and Preference 2 columns along with the PGP1 and PGP2 headcount columns. With the way my data is currently laid out the pivot tables create subsections and separate columns which is right but all the data should be combined. I would really like my final data too look like the 3rd table in the second tab but if I do it any other way it looks like the table in the 3rd tab I just created (New), I think I did what you mentioned. Thanks for your help again!
 

Attachments

  • Pivot Table Quetion.xlsx
    31 KB · Views: 22

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I don't really understand your reason, but no matter, here is an example using a calculated field
 

Attachments

  • XLGuru - 1053 - Pivot Table Quetion.xlsx
    30.3 KB · Views: 28

PraveshG81

New member
Joined
Oct 6, 2011
Messages
22
Reaction score
0
Points
0
Here is what I am trying to do: every applicant has an current status and that candidate can you also pick the Practice Group they have an interest in. That being said, if a person has an interest in two Practice Groups they are a .5 headcount in one practice group and another .5 in the second practice group. Or if they have an interest in only ONE practice group they are a 1 headcount in that Practice Group. On a sit note, the raw list of Practice Group Preference 1 is the same as Practice Group Preference 2.

At the end, what I am trying to see is how much headcount we have in each practice group (vertical) per current application status (horizontal). So for example, for Litigation Genera here is how the number should be summed per current status:

Callback Declined = 3
Callback Extended = 5
Callback Hold = 1.5
Callback Scheduled = 31.5
Offer Under Discussion = 3

Does this make more sense?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I think you will struggle to do that in the pivot if it is even possible.

Can we add extra columns to the Raw Data with calculations and work it out there?
 

PraveshG81

New member
Joined
Oct 6, 2011
Messages
22
Reaction score
0
Points
0
I am open to any other method, the content in the Raw Data is my export from our recruiting system. Is there anyway to copy what is in the Raw Data tab to another tab and then add the new column? If not, i can live with adding a new column at the end of the data in the Raw Data tab.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Took some effort, but I think I've got it
 

Attachments

  • XLGuru - 1053 - Pivot Table Question.xlsx
    23.2 KB · Views: 32

PraveshG81

New member
Joined
Oct 6, 2011
Messages
22
Reaction score
0
Points
0
THANK YOU SO MUCH! for helping me out with this, I think this is exactly what I need.

One last question, If i want to see by Law School, and within each laws what practice group have headcount and keep Current Status as my column heading should i add another columns Practice Group Preference call Law School and then use this formula: =IF(COUNTIFS($C:$C,C2,$D:$D,B2)=0,B2,D2) ?

I have attached the example.
 

Attachments

  • XLGuru - 1053 - Pivot Table Question.xlsx-1.XLS
    27.2 KB · Views: 16

PraveshG81

New member
Joined
Oct 6, 2011
Messages
22
Reaction score
0
Points
0
But, why are there decimals numbers, should i have either .5 or 1 show up in the "PG Total" column?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Why not just add the original School as a Row header, it should show as you want.
 

PraveshG81

New member
Joined
Oct 6, 2011
Messages
22
Reaction score
0
Points
0
Ya i could do that, i just thought I had to add a columns similar to "Practice Group Preference" anytime i wanted to add additional variables to the pivot tables analysis. But add the original School header to the Row header and it worked. But can you tell me why I am seeing decimal values for the head count when I add "School" to the Row Header? Shouldn't the head count always be .5 or 1 nothing?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
There is no need to add extra columns. The columns that I added were necessary to determine a split of the two practice times over the Preference. You now just want another level which is why you just need to add it to the rows pane.
 
Top