CODEWITHSUNDEEP
excelexcel-formulapivotpivot-tabledata-modeling
Anil Kumar
Anil Kumar

Reputation: 1

Need help in Pivot table or Formatting the data

I have a scenario where I have the list of promotions by each job level by Month. In December, the promotion receivership % is 6.6% and in June 2.5% however when I include both I was expecting 9.1%. However Pivot is giving me 4.6%. I know the reason, its because of the denominator is different for each month. I have pasted pivots screen print below. I need a help to understand how to modify the Pivot or modify my original data to get 9.1% overall and 6.6%/2.5% when I select the respective months.

I tried to rearrange the data but its not helping. I am looking for overall year 9.1% and Dec-6.6% and June 2.5%

Update: 15th July Sorry, for some reason the image did not post so I am adding file now.enter image description here

Upvotes: -1

Views: 66

Answers (1)

Chronocidal
Chronocidal

Reputation: 8016

Simplifying down slightly (by removing the Level), consider the following table of users:

[A] User [B] Month [C] Promoted?
A Dec 2023 TRUE
B Dec 2023 TRUE
C Dec 2023 TRUE
D Dec 2023 FALSE
E Dec 2023 FALSE
A Jun 2024 FALSE
C Jun 2024 FALSE
D Jun 2024 TRUE
F Jun 2024 TRUE

Now, to work things out for December or June, you just do a COUNTIF to get the number of Users, and a COUNTIFS to get the number of promotions, then you divide one by t'other:

[F] Month [G] Users [H] Promotion [I] Received
2023-12-01 =COUNTIF($B:$B,F2) =COUNTIFS($B:$B,F2,$C:$C,TRUE) =H2/G2
2024-06-01 =COUNTIF($B:$B,F3) =COUNTIFS($B:$B,F3,$C:$C,TRUE) =H3/G3
[F] Month [G] Users [H] Promotion [I] Received
Dec 2023 5 3 60%
Jun 2024 4 2 50%

However, for the total, it is more complicated: Users A, C, and D were present in both months. Users B and E were present in December, but had left by June. And User F joined at some point after December.

This means that, rather than a total of 9 users (with 5 promotions, for 55.56% received), we have a total of 6 unique users (in this example, still with 5 promotions). So, we need to count (or, rather COUNTA) the UNIQUE users, sometimes after FILTERing for a promotion:

[F] Month [G] Users [H] Promotion [I] Received
Total =COUNTA(UNIQUE($A$2:$A$10)) =COUNTA(UNIQUE(FILTER($A$2:$A$10,$C$2:$C$10))) =H4/G4
[F] Month [G] Users [H] Promotion [I] Received
Total 6 5 83.33%

As can (hopefully) be seen, this means that your data cannot be properly compared in this way from its aggregated form — and that a unique reference for each user that remains static between months is required: Depending on what your turnover is, the final percentage could be anywhere from 4.6% (if everyone left after December, and was replaced before June) to 8.9% (if people left after December, but no one new joined)

(Actually, there's a third extreme to consider: if 50400 people left the company after December, all 1200 who stayed had been promoted, then 47400 joined the company before June, but only the 1200 who had received promotions in December and stayed were promoted in June, then only 3400 people received promotions — albeit 1200 of them were promoted twice — out of 99000 unique employees, which gives a receivership of 3.43%)

Upvotes: 0

Related Questions