Workforce Turnover Dashboard — Power BI to Plotly Rebuild

JavaScript
Plotly
BI
Federal
Workforce Analytics
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.
Published

April 10, 2025

Overview

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

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.

NoteWhy this rebuild matters

Power BI delivers excellent self-service BI, but per-user licensing adds up across a large federal workspace. A Plotly equivalent embedded in any internal Quarto site — or a static page like this one — provides the same chart interactivity (hover, zoom, legend toggle, drill via filters) at zero recurring license cost. The pattern transfers to any dashboard whose primary value is communication, not authoring.

Try it

Tip

*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.

How the rebuild maps from Power BI

Power BI element Plotly / Quarto equivalent
Slicers (Separation FY, Gender, Retained) Inputs.select(...) with Observable JS reactivity
KPI cards (rate, average, median) Plain HTML/CSS computed from filtered rows
Bar + line combo chart Plotly bar + scatter with dual y-axes
Cross-tab tables (counts by category) d3.rollup over filtered rows + Plotly bar charts
DAX measures (COUNTROWS, DIVIDE, etc.) JavaScript reductions over the filtered array
Workspace governance Static page deploy; no per-user license
Data refresh (scheduled in Power BI Service) Re-run make_data.py; redeploy the JSON file

Trade-offs

What this rebuild gives up:

  • Row-level security & sensitivity labels. Power BI integrates with Azure AD for per-user data filtering; a static JSON file is visible to anyone who can load the page. For real data this would have to move server-side or stay on the intranet.
  • Authoring UI. Power BI’s drag-and-drop authoring is a real win for analysts who don’t code. The Plotly route trades authoring convenience for runtime cost.
  • Scheduled refresh. Power BI Service handles refresh automatically; this approach requires a deploy pipeline to regenerate the JSON.

What it adds:

  • Zero per-user cost. Anyone with a browser can view the dashboard.
  • Embeddable anywhere. The same Plotly bundle works in any HTML page, intranet portal, email export, or static site.
  • Reproducible from source. make_data.py + index.qmd fully describe the dashboard. Anyone with the repo can rebuild it identically.

Source

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 VA 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]

# Female-skewed workforce per the original dashboard's donut chart (~57% F)
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()
Back to top