Pivot Table & Calculated Fields?

[thaatrain]

Hi everyone,

Apologies if my question has been posted or solved before, however I could not find so here I go.

I have a series of HR headcount data, which contains various columns of 1's and 0's.

I am trying to figure out turnover or attrition, but I would like to calculate this within a pivot table itself, which therefore means I probably need a calculated to to sum up 2 desired columns and then divide the sum by the sum of the other column to arrive at a %tage.

I have attached a very basic example of what the background data looks like, hopefully it helps.

How do I sum up the totals of column C, separately sum up the the totals of column B, then divide the total of column B, by that of column C?

Column A Column B Column C Name Headcount July Turnover (leaver) July Employee 1 1 0 Employee 2 1 0 Employee 3 1 0 Employee 4 1 0 Employee 5 0 1 Employee 6 1 0 Employee 7 0 1 Employee 8 1 0 Employee 9 0 1 Employee 10 1 0 Employee 11 1 0 Employee 12 1 0 Employee 13 1 0 Employee 14 1 0 Employee 15 0 1 <tbody> </tbody>

<tbody>
</tbody>

Thanks in advance,

A.

 

[jakeman]

Quick question. Is the turnover rate in this example 36.36% or 4 turnovers / 11 Headcount?

 

[thaatrain]

Quick question. Is the turnover rate in this example 36.36% or 4 turnovers / 11 Headcount?

Hi,

Yes that's right.

 

[jakeman]

What you could do is add an additional column to your table and call it something like "Summary" and you'll want to repeat that word "Summary" for every employee that gets counted.

Next step would be for you to create your pivot table off of those three columns, such that when you create it you'll drop "Summary" into Rows and your "Headcount MMM" and "Turnover (leaver) MMM" into Values. Now you should see "Summary" with Headcounts and Turnovers all in the same line.

Lastly, click on your Pivot table (anywhere) and then select Analyze tab and select Fields, Items & Sets under Calculations. Select Calculated Field and then call it something like TurnoverRate. Your Formula will be ='Turnover (leaver) July'/'Headcount July'.

That will get where you need to be.

 

[thaatrain]

What you could do is add an additional column to your table and call it something like "Summary" and you'll want to repeat that word "Summary" for every employee that gets counted.

Next step would be for you to create your pivot table off of those three columns, such that when you create it you'll drop "Summary" into Rows and your "Headcount MMM" and "Turnover (leaver) MMM" into Values. Now you should see "Summary" with Headcounts and Turnovers all in the same line.

Lastly, click on your Pivot table (anywhere) and then select Analyze tab and select Fields, Items & Sets under Calculations. Select Calculated Field and then call it something like TurnoverRate. Your Formula will be ='Turnover (leaver) July'/'Headcount July'.

That will get where you need to be.

Hi,
Thanks for you reply. I'm not sure I understand or explained myself properly.

Below is a mock up of my data.

I would need to sum up the corresponding Year-On-Year totals then divide one by the other. I.e. Formula =Sum(Turnover phasing Jun-17)/sum(headcount Phasing Jun-18).

So on and so forth.

Is that possible in a pivot table calculated field?

Thanks for any help you can provide.

A.

	Headcount Phasing	Turnover Phasing
Name	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19
Employee 1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Employee 2	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Employee 3	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Employee 4	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Employee 5	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Employee 6	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Employee 7	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Employee 8	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Employee 9	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

<colgroup><col><col span="50"></colgroup><tbody>
</tbody>

 

[thaatrain]

**Sorry that should read: "Formula =Sum(Turnover phasing Jun-17)/sum(headcount Phasing Jun-17).

 

[RasGhul]

Is this what your looking for?

 

[thaatrain]

Is this what your looking for?

Oh my God, that is genius, and exactly what Im looking for!

Please teach me.

 

[RasGhul]

Create a copy of your Dates row (which is the same for Turnover & Headcount)
Sum All of your month columns from headcount ie =SUM(B3:B11) copy across until Jun-19
Sum All of your month columns from turnover ie =SUM(AB3:AB11) copy across until Jun-19

Then use =IFERROR(B15/B14,"") to get your "Formula =Sum(Turnover phasing Jun-17)/sum(headcount Phasing Jun-17)"

Then Click in this table that you have just made and create pivot from that, change Column headers names etc.

I recommend converting your original data into a table so that it updates your pivot.

https://docs.google.com/spreadsheet...Zu-MW-V3FbyXeZ9vdNCS7zP3fI/edit#gid=927044583

 

[thaatrain]

Oh thanks for this but I'm not sure it'll work for my real data set as my headcount and turnover data are not only captured by employee, but the employees are tagged geographically and functionally, & also type of turnover (voluntary/involuntary) so ideally I would want to pivot those by using calculated fields rather than external formulas.

I will also want to add pivot charts, so the pivot will only show what I have drilled to on slicer, rather than everything.

Please see below.

Personal Details	Gender Balance	Position Details	Headcount Phasing	Turnover Counting
Employee ID	Preferred Name (if different)	Gender	Job Title	Business Unit	Physical Location	Contract Status	Employee Type	Global Job Band	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19
1000001	Mike Tyson	F	Business Support Specialist	Americas Cluster	US	Permanent	Local	A	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
1000002	Donald Trump	F	Office Services Manager	Americas Cluster	US	Permanent	Local	B	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
1000003	Michael Jordan	F	Pricing & Promotion Mgr	Americas Cluster	US	Permanent	Local	B	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
1000004	Teresa May	F	Sr. Brand Mgr.	BU Domestic - US	US	Permanent	Local	D	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0

<colgroup><col><col><col><col><col span="3"><col><col span="51"></colgroup><tbody>
</tbody>