Medical Insurance Cost Analysis Dashboard

POWER BI • HEALTHCARE

Built a Power BI dashboard to analyze medical insurance charge patterns across key risk factors (age, BMI, smoking status, region, dependents). The solution enables underwriting-style segmentation, rapid drill-down, and repeatable pricing insights from a 1,338-record dataset.

Dataset: 1,338 records Key insight: smoking ≈ 4× avg charges Dims: age, BMI, smoker, region, children

Challenge

Manual Excel-based analysis created bottlenecks in underwriting workflow, with limited ability to identify cost patterns across multiple risk factors simultaneously.

Key Findings

  • Smoking status is the dominant driver: smokers show ~4× higher average charges than non-smokers in this dataset.
  • BMI segmentation is non-linear: average costs rise sharply in higher BMI bands, supporting tiered risk scoring.
  • Age concentration effect: older age bands represent a smaller share of members but account for a disproportionate share of total charges—useful for pricing and care-management targeting.

Data

  • Grain: one record per insured member
  • Fields: age, BMI, smoker, region, children, charges
  • Prep: Power Query normalization (BMI bands, type casting, category cleanup)
  • Note: portfolio dataset; insights reflect this sample and are intended to demonstrate analytic workflow.

Technical Execution

  • Modeling: built a clean semantic layer with measures for average charge, cost per capita, and segment comparisons.
  • DAX: created reusable measures for cohort filtering, variance vs baseline, and KPI cards.
  • UX: drill-through from KPI overview → segment view → member-level detail; slicers for region, smoker, BMI band, and age band.
  • Power Query: standardized categories and created calculated bands to support consistent segmentation.
Tech: Power BI, DAX, Power Query, Data Modeling Domain: Underwriting Analytics, Risk Segmentation

Outcome

Deliverable: an underwriting-style dashboard that supports fast segmentation and drill-down for pricing review.

  • Identify high-cost profiles for additional review (e.g., smoker + high BMI + older age).
  • Compare regional charge patterns and member mix to inform pricing assumptions.
  • Reduce manual slicing/pivoting by moving analysis into a repeatable, interactive dashboard workflow.
← Back to Projects