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?

[TABLE="width: 332"]
<tbody>[TR]
[TD][TABLE="width: 332"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Headcount July[/TD]
[TD]Turnover (leaver) July[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Employee 3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Employee 4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Employee 5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Employee 6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Employee 7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Employee 8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Employee 9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Employee 10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Employee 11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Employee 12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Employee 13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Employee 14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Employee 15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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;

[TABLE="width: 1196"]
<tbody>[TR]
[TD]date[/TD]
[TD]Employee ID[/TD]
[TD]Preferred Name (if different)[/TD]
[TD]Gender[/TD]
[TD]Job Title[/TD]
[TD]Business Unit[/TD]
[TD]Physical Location[/TD]
[TD]Contract Status[/TD]
[TD]Employee Type[/TD]
[TD]Global Job Band[/TD]
[TD]headcount[/TD]
[TD]turnover[/TD]
[/TR]
[TR]
[TD="align: right"]Jun-17[/TD]
[TD="align: right"]1000001[/TD]
[TD]Mike Tyson[/TD]
[TD]F[/TD]
[TD]Business Support Specialist[/TD]
[TD]Americas Cluster[/TD]
[TD]US[/TD]
[TD]Permanent[/TD]
[TD]Local[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Jun-17[/TD]
[TD="align: right"]1000001[/TD]
[TD]Mike Tyson[/TD]
[TD]F[/TD]
[TD]Business Support Specialist[/TD]
[TD]Americas Cluster[/TD]
[TD]US[/TD]
[TD]Permanent[/TD]
[TD]Local[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]Jul-17[/TD]
[TD="align: right"]1000001[/TD]
[TD]Mike Tyson[/TD]
[TD]F[/TD]
[TD]Business Support Specialist[/TD]
[TD]Americas Cluster[/TD]
[TD]US[/TD]
[TD]Permanent[/TD]
[TD]Local[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

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.