Who Is Leaving Lumina Tech, When, and What It Costs

Leadership could see people leaving but had no costed figure to justify retention spending. Standard headcount reports showed who left and when. They did not show what those departures cost, or where the cost concentrated.

The source is a 1,000-row employee sheet with job title, department, business unit, salary, hire date, exit date and location. 85 rows have an exit date.

Steps applied

1. Removed personal fields not needed for the question: full name, ethnicity, latitude and longitude. A cost-of-turnover view needs no names, and dropping them keeps the model lean and privacy-safe.
2. Set explicit types for every column, including true date types for hire and exit dates.
3. Added a leaver flag: 1 if an exit date exists, otherwise 0.
4. Added exit year from the exit date for the trend chart.
5. Calculated tenure at exit as the days between hire and exit divided by 365.25, so leap years do not distort it.
6. Grouped tenure into bands: under 1 year, 1 to 2, 2 to 5, and 5 or more. Each band starts with a number so it sorts in the right order.

Table.AddColumn(Tenure, "Tenure Band", each
    let t = [#"Tenure at Exit (yrs)"] in
    if t = null then null
    else if t < 1 then "1. Under 1 yr"
    else if t < 2 then "2. 1-2 yrs"
    else if t < 5 then "3. 2-5 yrs"
    else "4. 5+ yrs",
  type text)

Turnover cost is not recorded in any HR system. It has to be estimated, and the estimate depends on an assumption. Rather than hide that assumption, the dashboard puts it on a slider.

Est Turnover Cost =
[Salary of Leavers] * 'Replacement Cost Pct'[Replacement Cost Pct Value] / 100

Why 30%

Published estimates of the full cost of replacing an employee vary widely, from under a third of annual salary for junior roles to more than a full year's salary for specialist and senior roles. 30% sits at the conservative end of that range, so the headline figure is unlikely to overstate the problem.

• What 30% coversRecruitment, onboarding and the productivity lost while a new hire gets up to speed, as a single rate.
• Same rate for every roleA senior engineer and a junior analyst are costed at the same share of salary, which understates specialist exits.
• Linear scalingDoubling the rate doubles the cost. At 50% the estimate is about $4.44M.
• Agree it firstThe rate should be agreed with Finance before the figure goes into a business case.

Marketing and Engineering hold just over a quarter of the workforce but account for 38% of leavers and 43% of the estimated cost.

• Tenure pattern: 20 leavers went in their first year, 13 between one and two years, 22 between two and five, and 30 after five or more. Average tenure at exit was 4.9 years.
• Trend: exits were occasional before 2013, then rose sharply from 2017 and peaked at 20 in 2021.
• IT is the largest department but has the lowest leaver rate, 6.6%.

• Not an annual rate. Exit dates span 1994 to 2022, so the 8.5% leaver rate is cumulative across the whole period. It should not be compared with a yearly attrition benchmark.
• No reason for leaving. The data does not record voluntary versus involuntary exits, so regretted attrition cannot be separated out.
• Cost is an estimate. It rests on the assumption in section 04 and should be read as a range, not a fact.
• Case-study data. Figures describe a fictional company and are not results from an employer.

1. Prioritise Marketing and Engineering. Run exit and stay interviews in both to find the causes before committing budget.
2. Strengthen the first year. Structured 30, 60 and 90-day check-ins would target the 20 people who left before their first anniversary.
3. Review progression for long-serving staff. 30 leavers had five or more years' service, which points to limited routes upward.
4. Agree the cost rate with Finance, then use the slider to present a low, central and high estimate rather than a single figure.
5. Record exit reasons so future reporting can separate regretted from non-regretted attrition and track it by year.

Download the Power BI file (Lumina_Attrition_Cost.pbix). It opens in Power BI Desktop with the data model and every measure described here.

1. Load the employee sheet and apply the Power Query steps in section 02. Name the query Employees.
2. Set Exit Year to "Don't summarize" so it works as a chart axis.
3. Create the measures in section 03 and the parameter in section 04: 0 to 100, increment 10, default 30.
4. Sort the tenure chart by Tenure Band ascending so the bands read in order.