Workforce Turnover Dashboard Power BI to Plotly Rebuild

An interactive workforce-turnover dashboard rebuilt in Plotly.js as a proof-of-concept open-source alternative to a Power BI report I maintain at a federal agency. Filters, KPI cards, monthly combo chart, and cross-tabs by occupational series and separation reason all client-side, no licensing cost.

Overview

I maintain a Power BI dashboard for leadership at a federal agency: monthly turnover trends, retention rates, demographic cross-tabs, the works. This page is a proof-of-concept rebuild of it in pure JavaScript with Plotly.js, showing that the same self-service analytics can be delivered entirely in open-source tooling.

It is a hypothetical mockup, not a production system. It runs on synthetic data and is not deployed inside the agency, which has no suitable web server to host it; treat it as a high-level example of what the open-source approach can look like.

The data on this page is synthetic. A Python script (make_data.py) generates ~5,000 mock separation records with realistic distributions (gender split, age groups, occupational series codes, separation reasons, December-spike seasonality) and serializes them to JSON. The dashboard then loads that JSON once and computes everything client-side from there. No real federal records are used.

Try it

Plotly = require("https://cdn.plot.ly/plotly-2.35.2.min.js")
raw = FileAttachment("turnover.json").json()

viewof fyChoice = Inputs.select(["All", 2023, 2024], {label: "Separation FY", value: "All", format: d => String(d)})
viewof genderChoice = Inputs.select(["All", "F", "M"], {label: "Gender", value: "All"})
viewof retainedChoice = Inputs.select(["All", "Retained", "Not Retained"], {label: "Retained*", value: "All"})

filtered = raw.rows.filter(r => {
  if (fyChoice !== "All" && r.fy !== fyChoice) return false
  if (genderChoice !== "All" && r.gender !== genderChoice) return false
  if (retainedChoice === "Retained" && !r.retained) return false
  if (retainedChoice === "Not Retained" && r.retained) return false
  return true
})

yearsInView = fyChoice === "All" ? raw.meta.fiscal_years.length : 1
genderHeadcount = genderChoice === "All"
  ? raw.meta.headcount
  : Math.round(raw.meta.headcount * (genderChoice === "F" ? 0.572 : 0.428))
fyAnnualizedRate = filtered.length === 0 ? 0
  : (filtered.length / genderHeadcount / yearsInView * 100)
medianMonths = (() => {
  if (filtered.length === 0) return 0
  const sorted = filtered.map(r => r.months_total).sort((a, b) => a - b)
  const mid = Math.floor(sorted.length / 2)
  return sorted.length % 2 ? sorted[mid] : (sorted[mid-1] + sorted[mid]) / 2
})()
avgMonthlySeparations = Math.round(filtered.length / (12 * yearsInView))

htl.html`<div style="display:grid;grid-template-columns:repeat(3,1fr);gap:1rem;margin:1.5rem 0;">
  <div style="background:#0072B2;color:white;padding:1.2rem;border-radius:6px;text-align:center;">
    <div style="font-size:0.85rem;opacity:0.85;text-transform:uppercase;letter-spacing:0.05em;">FY Annualized Rate</div>
    <div style="font-size:2.4rem;font-weight:bold;margin-top:0.3rem;">${fyAnnualizedRate.toFixed(2)}%</div>
  </div>
  <div style="background:#009E73;color:white;padding:1.2rem;border-radius:6px;text-align:center;">
    <div style="font-size:0.85rem;opacity:0.85;text-transform:uppercase;letter-spacing:0.05em;">Avg Monthly Separations</div>
    <div style="font-size:2.4rem;font-weight:bold;margin-top:0.3rem;">${avgMonthlySeparations.toLocaleString()}</div>
  </div>
  <div style="background:#CC79A7;color:white;padding:1.2rem;border-radius:6px;text-align:center;">
    <div style="font-size:0.85rem;opacity:0.85;text-transform:uppercase;letter-spacing:0.05em;">Median Tenure (months)</div>
    <div style="font-size:2.4rem;font-weight:bold;margin-top:0.3rem;">${medianMonths.toFixed(0)}</div>
  </div>
</div>`

monthlyData = {
  const months = [10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9]
  const monthLabels = ["Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep"]
  const counts = months.map(m => filtered.filter(r => r.fy_month === m).length)
  const rates = counts.map(c => yearsInView > 0
    ? (c * 12 / yearsInView) / genderHeadcount * 100
    : 0)
  return {months, monthLabels, counts, rates}
}

monthlyChart = {
  const wrapper = html`<div class="plotly-wrapper"></div>`
  const div = html`<div style="height:480px;"></div>`
  wrapper.appendChild(div)
  const traces = [
    {
      type: "bar",
      name: "Employees Lost",
      x: monthlyData.monthLabels,
      y: monthlyData.counts,
      marker: {color: "#0072B2"},
      hovertemplate: "<b>%{x}</b><br>%{y:,} separations<extra></extra>",
    },
    {
      type: "scatter",
      mode: "lines+markers",
      name: "FY Annualized Rate",
      x: monthlyData.monthLabels,
      y: monthlyData.rates,
      yaxis: "y2",
      line: {color: "#D55E00", width: 3},
      marker: {size: 8},
      hovertemplate: "<b>%{x}</b><br>%{y:.2f}% annualized<extra></extra>",
    },
  ]
  const layout = {
    title: {text: "Monthly Separations & Annualized Rate", font: {size: 16}},
    xaxis: {title: "FY Month"},
    yaxis: {title: "Employees Lost", side: "left"},
    yaxis2: {title: "Annualized Rate (%)", overlaying: "y", side: "right", showgrid: false, ticksuffix: "%"},
    legend: {orientation: "h", y: -0.2},
    margin: {l: 60, r: 60, t: 50, b: 80},
    bargap: 0.2,
  }
  Plotly.newPlot(div, traces, layout, {responsive: true, displayModeBar: false})
  return wrapper
}

occCrossTab = {
  const groups = d3.rollup(
    filtered,
    v => v.length,
    r => r.occ_code,
    r => r.occ_name,
  )
  const rows = []
  for (const [code, byName] of groups) {
    for (const [name, count] of byName) {
      rows.push({code, name, count})
    }
  }
  rows.sort((a, b) => b.count - a.count)
  return rows.slice(0, 10)
}

occChart = {
  const wrapper = html`<div class="plotly-wrapper"></div>`
  const div = html`<div style="height:420px;"></div>`
  wrapper.appendChild(div)
  const traces = [{
    type: "bar",
    orientation: "h",
    x: occCrossTab.map(r => r.count).reverse(),
    y: occCrossTab.map(r => `${r.code}  ${r.name}`).reverse(),
    marker: {color: "#009E73"},
    hovertemplate: "<b>%{y}</b><br>%{x:,} separations<extra></extra>",
  }]
  const layout = {
    title: {text: "Top 10 Occupational Series by Separations", font: {size: 16}},
    xaxis: {title: "Separations"},
    yaxis: {automargin: true},
    margin: {l: 20, r: 30, t: 50, b: 50},
  }
  Plotly.newPlot(div, traces, layout, {responsive: true, displayModeBar: false})
  return wrapper
}

noaCrossTab = {
  const groups = d3.rollup(
    filtered,
    v => ({
      retained: v.filter(r => r.retained).length,
      notRetained: v.filter(r => !r.retained).length,
    }),
    r => r.noa_desc,
  )
  const rows = Array.from(groups, ([noa, counts]) => ({
    noa,
    retained: counts.retained,
    notRetained: counts.notRetained,
    total: counts.retained + counts.notRetained,
  }))
  rows.sort((a, b) => b.total - a.total)
  return rows
}

noaChart = {
  const wrapper = html`<div class="plotly-wrapper"></div>`
  const div = html`<div style="height:420px;"></div>`
  wrapper.appendChild(div)
  const traces = [
    {
      type: "bar",
      name: "Not Retained (≤24 mos)",
      x: noaCrossTab.map(r => r.notRetained),
      y: noaCrossTab.map(r => r.noa),
      orientation: "h",
      marker: {color: "#CC79A7"},
      hovertemplate: "<b>%{y}</b><br>Not retained: %{x:,}<extra></extra>",
    },
    {
      type: "bar",
      name: "Retained (>24 mos)",
      x: noaCrossTab.map(r => r.retained),
      y: noaCrossTab.map(r => r.noa),
      orientation: "h",
      marker: {color: "#0072B2"},
      hovertemplate: "<b>%{y}</b><br>Retained: %{x:,}<extra></extra>",
    },
  ]
  const layout = {
    title: {text: "Separations by Reason  Retained vs Not Retained", font: {size: 16}},
    xaxis: {title: "Separations"},
    yaxis: {automargin: true},
    barmode: "stack",
    legend: {orientation: "h", y: -0.15},
    margin: {l: 20, r: 30, t: 50, b: 80},
  }
  Plotly.newPlot(div, traces, layout, {responsive: true, displayModeBar: false})
  return wrapper
}

occChart

noaChart

NoteRetention definition

Retained is defined the same way as in the original dashboard: an employee who has been onboard greater than 24 months. The “Not Retained” group captures early separations historically a stronger signal of recruitment-and-retention friction than the headline turnover rate.

Source

The dashboard runs entirely client-side: Plotly.js renders the charts, D3 (d3.rollup) builds the occupational-series and separation-reason cross-tabs, and Observable’s reactive runtime wires the filter inputs to every visual. The code for each visual is available in the Code fold above it. The synthetic-data generator is below.

make_data.py

"""Generate synthetic workforce-turnover data for the dashboard.

Produces `turnover.json` — a list of mock separation records loosely modeled
on the distributions visible in the original Power BI dashboard (gender
57/43, ~7.5% annualized turnover, December separation spike, etc.). Numbers
are fictitious — no real federal data is used.

Run from this folder:

    python make_data.py
"""

import json
import math
import os
import random
from datetime import date, timedelta

HERE = os.path.dirname(os.path.abspath(__file__))
OUT = os.path.join(HERE, "turnover.json")

random.seed(2026)

# Population size and broad turnover target
TOTAL_HEADCOUNT = 35_000
TARGET_FY_RATE = 0.075   # ~7.5% annualized matches the dashboard's headline
FISCAL_YEARS = [2023, 2024]

# Occupational series — real federal classification codes with realistic mix
OCC_SERIES = [
    ("0301", "Miscellaneous Administration & Program", 0.18),
    ("0343", "Management & Program Analysis", 0.10),
    ("0905", "General Attorney", 0.03),
    ("1101", "General Business & Industry", 0.12),
    ("1102", "Contracting", 0.07),
    ("0201", "Human Resources Management", 0.08),
    ("0560", "Budget Analysis", 0.04),
    ("2210", "Information Technology Management", 0.11),
    ("0996", "Veterans Claims Examining", 0.15),
    ("0930", "Hearings & Appeals", 0.06),
    ("0991", "Workers' Compensation Claims Examining", 0.03),
    ("0080", "Security Administration", 0.03),
]

# NOA_DESC = Nature of Action description
NOA_DESC = [
    ("Other Separation", 0.43),
    ("Retirement", 0.19),
    ("Crossover", 0.14),
    ("Reassignment", 0.12),
    ("Reduction in Force", 0.04),
    ("Removal", 0.03),
    ("Death", 0.02),
    ("Conversion", 0.03),
]

AGE_GROUPS = [
    ("24 and under", 0.04),
    ("25-34", 0.18),
    ("35-44", 0.26),
    ("45-54", 0.24),
    ("55-64", 0.20),
    ("65+", 0.08),
]

LOSE_GRADES = ["GS-05", "GS-07", "GS-09", "GS-11", "GS-12", "GS-13", "GS-14", "GS-15"]
LOSE_GRADE_WEIGHTS = [0.06, 0.10, 0.12, 0.16, 0.20, 0.20, 0.12, 0.04]


GENDER_WEIGHTS = {"F": 0.572, "M": 0.428}

# Monthly distribution within an FY (Oct → Sep), with the December spike
# visible in the original dashboard (~11% of the year in December).
MONTHLY_WEIGHTS = {
    10: 0.0789, 11: 0.0755, 12: 0.1099, 1: 0.0851, 2: 0.0801,
    3: 0.0815, 4: 0.0900, 5: 0.0808, 6: 0.0767, 7: 0.0780,
    8: 0.0736, 9: 0.0285,  # September tails off as the FY closes
}


def weighted_choice(pairs):
    """pairs = [(value, weight), ...] -> value"""
    total = sum(w for _, w in pairs)
    r = random.random() * total
    upto = 0
    for v, w in pairs:
        upto += w
        if upto >= r:
            return v
    return pairs[-1][0]


def fy_month_to_date(fy: int, fy_month: int) -> date:
    """FY month 1 = October of (fy - 1); FY month 12 = September of fy."""
    if fy_month >= 10:
        calendar_year = fy - 1
    else:
        calendar_year = fy
    day = random.randint(1, 28)
    return date(calendar_year, fy_month, day)


def generate():
    rows = []

    for fy in FISCAL_YEARS:
        # Target separations this FY = rate * headcount, with mild noise.
        n_separations = int(TOTAL_HEADCOUNT * TARGET_FY_RATE * random.uniform(0.92, 1.08))

        for _ in range(n_separations):
            fy_month = weighted_choice(list(MONTHLY_WEIGHTS.items()))
            sep_date = fy_month_to_date(fy, fy_month)
            gender = "F" if random.random() < GENDER_WEIGHTS["F"] else "M"
            age_group = weighted_choice([(a, w) for a, w in AGE_GROUPS])
            occ_code, occ_name, _ = random.choices(
                OCC_SERIES, weights=[w for *_, w in OCC_SERIES], k=1
            )[0]
            noa = weighted_choice(NOA_DESC)
            grade = random.choices(LOSE_GRADES, weights=LOSE_GRADE_WEIGHTS, k=1)[0]

            # Tenure in months; retirees skew high, crossovers/conversions low.
            if noa == "Retirement":
                months_total = int(random.gauss(280, 80))
            elif noa in ("Crossover", "Conversion"):
                months_total = int(random.gauss(22, 14))
            elif noa == "Reduction in Force":
                months_total = int(random.gauss(60, 40))
            else:
                months_total = int(random.gauss(48, 36))
            months_total = max(1, months_total)

            # "Retained" footnote in the original: onboard > 24 months.
            retained = months_total > 24

            rows.append({
                "sep_date": sep_date.isoformat(),
                "fy": fy,
                "fy_month": fy_month,
                "gender": gender,
                "age_group": age_group,
                "occ_code": occ_code,
                "occ_name": occ_name,
                "noa_desc": noa,
                "lose_grade": grade,
                "months_total": months_total,
                "retained": retained,
            })

    return rows


def main():
    rows = generate()
    payload = {
        "meta": {
            "headcount": TOTAL_HEADCOUNT,
            "fiscal_years": FISCAL_YEARS,
            "generated_seed": 2026,
            "note": "Synthetic data — no real federal records were used.",
        },
        "rows": rows,
    }
    with open(OUT, "w", encoding="utf-8") as f:
        json.dump(payload, f, separators=(",", ":"))
    print(f"Wrote {len(rows):,} synthetic separations to {OUT}")
    print(f"File size: {os.path.getsize(OUT)/1024:.1f} KB")


if __name__ == "__main__":
    main()