Forum Discussion
Employee Turnover
Hey Smoupre,
Thanks for replying and i tried alot but failed to get the output am pasting a sample data for your reference.
| Month | Active Employees | Separated Employees | Turnover Formula | Turnover % |
| Jan | 100 | 10 | 10/100 | 10% |
| Feb | 105 | 5 | 5/100 | 5% |
| March | 106 | 3 | 3/100 | 3% |
| April | 109 | 2 | 2/106 | 2% |
| May | 112 | 15 | 15/106 | 14% |
| June | 115 | 20 | 20/106 | 18% |
| July | 118 | 5 | 5/118 | 4% |
| Aug | 120 | 15 | 15/118 | 13% |
| Sept | 121 | 25 | 25/118 | 21% |
| Oct | 125 | 21 | 21/125 | 17% |
| Nov | 130 | 18 | 18/125 | 14% |
| Dec | 150 | 9 | 9/125 | 7% |
| Yearly Turn Over | (jan+April+July+Oct)/4 | 148 | 148/(100+109+118+125)/4 | 8% |
This is just to give you an idea. Looking forward for the solution, as my presentation is due bby next week and i got stuck with this dynamic formula.
Regards,
Muhammad Siddiq
MuhammadSiddiq,
Create the following columns in your table.
Quarter = IF(OR(OR(Table[Month] = "Jan",Table[Month] = "Feb"),Table[Month] = "March"),1,IF(OR(OR(Table[Month] ="April",Table[Month] ="May"),Table[Month] ="June"),2,IF(OR(OR(Table[Month] = "July",Table[Month] ="Aug"),Table[Month] ="Sept"),3,4)))
MinEmployee = CALCULATE(MIN(Table[Active Employees]),FILTER(Table,Table[Quarter]=EARLIER(Table[Quarter])))
Turnover = Table[Separated Employees]/Table[MinEmployee]
Then create the following measures in your table.
sum employees = SUM(Table[Separated Employees])
summinemployee = SUM(Table[MinEmployee])/3
Year Turn over = DIVIDE( [sum employees]/[summinemployee],MAX(Table[Quarter]))
Regards,
Lydia
Hey Lydia,
Thanks for the solution its works. But whan will be solution if the data is available in two seperate table one is of ACTIVE EMPLOYEE and the other one is of SEPARATED EMPOYEE and both are uploaded to data model then how we can calculate the same turnover by using the same formula. Below are the sample table for you reference.
Name Month Status
Alex Jan Active Maria Feb Active Maria Feb Active Maria Feb Active Sid Feb Active Zahid March Active Zoe March Active Zeus March Active Kahisf April Active Kahisf April Active Carol April Active Martin May Active Wick May Active Ricku\y May Active Rex June Active Ted June Active Juliana Juy Active Julia Juy Active Arman August Active Arman August Active Nancy Sept Active Matthew Sept Active Bela Oct Active Betta November Active Oliver November Active Name Month Status
Siddiq Jan Separated Sahid Feb Separated John Feb Separated Johny March Separated Terry April Separated Martin May Separated Luther June Separated Katherine Juy Separated Williams August Separated Bony Sept Separated Bretta Oct Separated Carla November Separated Looking for a solution from you for making it dynamic.
Regards,
Muhammad Siddiq
I can also share sample excel file for best understanding.
Can you just use an Append query to append the two tables together or is there a reason you need them in two separate tables?
Hey Smoupre,
I can append both tables together also there is no special reason to use both tables separate. The only reason for using both tables separated is to build relationship and to make the formula more dynamic and functioning. The calculated measure can work for both Month slicer and year slicer also.
Looking forward some magic from you guys.
Regards,
Muhammad Siddiq
