You only calculate turnover correctly in Power BI when the leavers from your HR system come along, you count people and employment contracts separately, and contractors have their own place in the model. If one of those three is missing, turnover shows zero or too low and headcount counts double.
This article is about the question management asks first and that most often gets a wrong answer: how many people have left, and how many do we still have?
Why turnover in Power BI shows zero
Turnover is a count of people who are no longer there. Those are exactly the people who drop off the list when you ask the HR system an ordinary question. Anyone who has left does not come along, and then turnover looks like zero.
Where that sits differs per HR system:
- HiBob returns only active employees by default. You have to request inactive employees separately, and the integration's service user needs access to that group.
- BuddeeHR archives everyone who leaves. You have to fetch archived employees along with their end date.
- Workday can exclude terminated workers and ended contingent workers. If you leave them in, the reason for leaving comes along too.
That reason is worth more than it seems. With the reason you can separate voluntary and involuntary turnover, for example per country. That only works if the HR system records the reason. Workday and Deel do.
Why headcount counts double
Headcount goes wrong in the other direction. There are more people in the report than actually work there.
The most common cause is a person with more than one employment contract. Visma Raet YouForce, SAP SuccessFactors and Oracle HCM allow several employment contracts per person. Someone with two appointments should count once in headcount and twice in the establishment (formatie, the count of positions). If you do not separate person and employment contract, you get one of those two figures wrong.
Other causes:
- Duplicate job rows from BambooHR. BambooHR sends all table rows of an employee with every change. If you keep adding those rows, Power BI counts the same job several times. Replacing the full history per employee solves that.
- A changed employee number. In BambooHR the employee number can be edited. If you match on that number, a changed number becomes a new employee. The internal ID does not change and is therefore the better key.
- Contractors in headcount. Deel records the type of employment: own entity, employer of record or contractor. Without that dimension in the model, contractors simply sit in your headcount.
Three choices to agree up front
Much of the discussion about turnover figures is really about definitions. If those are not fixed, every report counts in its own way. So agree on three things at the start.
What is a departure? Does an internal move count? In Oracle HCM a departure is the end of the work relationship. A change of assignment within the same employer is not a departure there. If you choose that definition, a transfer no longer counts as turnover.
What is a person and what is an employment contract? You count headcount in people and the establishment in employment contracts. Both figures can sit side by side in the model, as long as the two do not get mixed up.
Do contractors count? That is your choice. If the type of employment is a filter in the model, headcount with and without contractors sits side by side and nobody has to choose which figure is the right one.
More than a count
Once leavers, employment contracts and contractors are properly in the model, figures come within reach that you used to calculate by hand.
- Temporary contracts. BuddeeHR numbers contracts within an employment. In Power BI, HR can see who is on their third temporary contract and when it ends.
- Average length of employment. A derived measure like this belongs in the model as a fixed measure, so everyone uses the same calculation.
- Turnover over time. Turnover per month or per quarter needs a history that does not shift with every change. How you build it is explained in the article why an old HR report in Power BI changes and on history by reference date.
How KoppelHet builds this
At the start, KoppelHet agrees with HR what a departure is, what a person is and what an employment contract is, and whether contractors count. Those choices are then fixed in the model, so every report on it counts the same way. We fetch leavers and archived employees, keep person and employment contract separate and match on a key that does not change. Derived measures go into the model as fixed measures.
We build that per organisation, with building blocks that keep coming back and the rules that belong to your HR system. Examples are HiBob to Power BI, Workday to Power BI and Deel to Power BI.
The whole topic is on turnover and headcount, and the overview on HR data to BI. If it is about leaving in the sense of accounts and access, joiner, mover, leaver is a better fit. More about the systems: HiBob, BambooHR and Power BI.