Workers Compensation Analytics Dashboard
The Problem
Business Continuity stakeholders requested an Excel-based analytics dashboard to analyze workers compensation claims and identify risk patterns for safety improvements and cost reduction. The request came with a 3-day turnaround requirement and was outside the scope of standard workforce management responsibilities, requiring the project to be completed alongside regular job duties. Dataset contained claims across multiple lines of business with injury types, body parts, ages, and costs. Required rapid analysis to identify meaningful trends, quantify risk patterns, detect outliers, and deliver actionable recommendations. Dashboard needed to be executive-ready with professional design and clear insights for ongoing risk management.
The Solution
Built interactive Excel dashboard using Power Pivot data model with DAX calculated columns and measures. Created four-layer architecture: (1) Data layer importing source data to Power Pivot, (2) Calculation layer with DAX logic (approval status detection, age bucketing, percentage calculations, context-independent totals), (3) Analysis layer with hidden pivot tables, (4) Presentation layer with KPI cards, slicers, and visualizations.
Dashboard features KPI cards with smart K/M formatting via nested GETPIVOTDATA formulas, interactive slicers (year, injury type, status, age ranges, lines of business), and multiple visualizations (top 10 body part injuries, year-over-year trends, age range analysis, approval status breakdown). Delivered comprehensive analysis identifying highest risk line of business (nearly half of all claims), cost anomalies in lower-volume departments, high-cost outlier claims significantly inflating age range averages, and injury prevention opportunities targeting specific injury types and age demographics.
Architecture
Four-layer Excel Power Pivot architecture. Data layer imports source claims data to Power Pivot Data Model. Calculation layer uses DAX calculated columns (IF for approval status, VAR/SWITCH for age bucketing) and DAX measures (DIVIDE for percentages, CALCULATE with ALL for context-independent totals). Analysis layer contains hidden pivot tables connected to Data Model. Presentation layer features KPI cards using nested GETPIVOTDATA formulas with K/M formatting, interactive slicers (year, injury type, status, age, LOB), and multiple chart types (bar, column, pie) for executive-ready visualization.
Key Implementation Decisions
- •Power Pivot within Excel: Met stakeholder platform requirement while enabling advanced DAX and data modeling beyond standard pivot tables
- •Four-layer architecture: Separation of data, calculation, analysis, and presentation layers ensured maintainability despite aggressive timeline
- •Hidden pivot tables: Analysis layer hidden from view, presentation layer shows only polished visuals
- •CALCULATE with ALL: Context manipulation ensured percentage denominators remained constant across filters
- •Nested GETPIVOTDATA: Dynamic KPI cards with K/M formatting without VBA macros
- •Statistical outlier identification: High-cost outlier claim analysis revealed significant age range average inflation
- •Parallel execution: Managed project alongside regular workforce management duties within 3-day deadline
The Results
Quantifiable Outcomes
- ✓Stakeholders adopted dashboard for ongoing risk analysis and safety planning
- ✓Identified high-cost statistical outlier and quantified significant age range inflation impact
- ✓Delivered 5 actionable recommendations (denial rate investigation, safety training, injury prevention programs)
- ✓Dashboard requested for future analysis capabilities and ongoing monitoring
- ✓Demonstrated advanced Excel and DAX proficiency (Power Pivot, CALCULATE with ALL, SWITCH with TRUE(), nested GETPIVOTDATA)
- ✓Professional executive-ready design enabling data-driven safety decisions
Lessons Learned
- →Statistical outlier analysis provides high-value insights: Single high-cost claim significantly skewed age range averages, revealing hidden patterns
- →Context manipulation critical for accurate metrics: CALCULATE with ALL() ensured percentage denominators remained constant across filters
- →GETPIVOTDATA enables dynamic KPI cards: Nested formulas with K/M formatting created professional dashboard without VBA
- →Multi-dimensional analysis reveals actionable patterns: LOB × Age × Injury Type cross-analysis identified specific prevention targets
- →Executive presentation requires clear insight hierarchy: Organized findings from highest impact risks to specific recommendations