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.

 

[RasGhul]

Ok so I achieved a Calculated percentage field between headcount & turnover but with a change to the dataset. I placed your Dates in rows to the left of Employee ID and Headcount & Turnover get their own columns.

ie as per your real data for example Mike Tyson;

date	Employee ID	Preferred Name (if different)	Gender	Job Title	Business Unit	Physical Location	Contract Status	Employee Type	Global Job Band	headcount	turnover
Jun-17	1000001	Mike Tyson	F	Business Support Specialist	Americas Cluster	US	Permanent	Local	A	1	0
Jun-17	1000001	Mike Tyson	F	Business Support Specialist	Americas Cluster	US	Permanent	Local	A	1	1
Jul-17	1000001	Mike Tyson	F	Business Support Specialist	Americas Cluster	US	Permanent	Local	A	1	0

<tbody>
</tbody>

So now there are separate entries for date & HC or TO per employee and their geo/functional data which allows for the data to be sliced as desired.

 

[thaatrain]

Hi,

Thanks for this, but I cant change the raw data set. I can only have one line item per employee. Also you seem to be pivoting the informoation in order to perform a sum formula at the bottom, outside of the pivot;whereas I would need to calculation in the pivot itself.

Hope someone can help!?!?

 

[thaatrain]

Please help anyone?

 

[jakeman]

What you are asking for cannot be easily done (if at all) without modifying your source data the way I mentioned earlier or like RasGhul more elaborately mentioned. You don't have to change your source data but you will have to keep a secondary data set that you can use to calculate turnover in the pivot table.