Forum Discussion
Employee Turnover
This looks like an interesting problem. Can you provide some example data and expected output for this? This would really help understand you problem.
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,
LydiaHey 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
