Forum Discussion
Employee Turnover
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.
- Greg_Deckler
Community Champion
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
MuhammadSiddiq,
Please help to post expected result based on the new sample tables you shared.
Regards,
Lydia
