🚗 Auto Insurance Claims Analysis Using Power BI

Insurance companies continuously collect vast amounts of claims data. However, turning this data into actionable insights requires strong data modeling, visualization, and interpretation skills.

The purpose of this project is how to use Power BI for aggregating, visualizing, and interpreting key business metrics in the insurance domain. This simple dashboard showcases an interactive, data-driven Power BI dashboard to analyze auto insurance claims and extract insights across dimensions such as customer demographics, vehicle make, claim amounts, regional trends, and performance.

 

Data Transformation with Power Query

Power Query was used for:

  • Removing nulls and duplicates

  • Standardizing column types

  • Extracting date parts (e.g., Year, Month)

  • Creating dimension tables like DimDate, CarAgeGroup, and DimCustomer

Calculated Columns & Measures

Example Calculated Columns:


Car Age group = 
SWITCH(TRUE(),
    vehicles[Year] >= 1900 && vehicles[Year] <= 1950, "1900-1950",
    vehicles[Year] >= 1951 && vehicles[Year] <= 1960, "1951-1960",
    vehicles[Year] >= 1961 && vehicles[Year] <= 1970, "1961-1970",
    vehicles[Year] >= 1971 && vehicles[Year] <= 1980, "1971-1980",
    vehicles[Year] >= 1981 && vehicles[Year] <= 1990, "1981-1990",
    vehicles[Year] >= 1991 && vehicles[Year] <= 2000, "1991-2000",
    vehicles[Year] >= 2001 && vehicles[Year] <= 2010, "2001-2010",
    vehicles[Year] >= 2011 && vehicles[Year] <= 2020, "2011-2020",
    vehicles[Year] > 2020, ">2020",
    "Unknown"
)

Key Measures:


Total Premium Earned = SUM(FactClaims[PremiumAmount])

Total Claims Paid = SUM(FactClaims[ClaimAmount])

Underwriting Profit = [Total Premium Earned] - [Total Claims Paid]

Avg Claim Amount = AVERAGE(FactClaims[ClaimAmount])

Total Claims = COUNTROWS(FactClaims)

Avg Claims Per Year = DIVIDE([Total Claims], DISTINCTCOUNT(DimDate[Year]))

Dashboard Overview

The dashboard was designed for both summary and drill-down insights:

KPI Cards:

  • Premium Earned: $111M

  • Claims Paid: $19.12M

  • Underwriting Profit: $92M

  • Avg Claims per Year: 374

  • Avg Claim Amount: $3K

  • Avg Premium per Customer: $15K

Visuals:

  • Bar Charts: Claims by vehicle make & model, gender and marital status

  • Map: Regional distribution of claims paid

  • Stacked Bars: Claim rate by age group

  • Slicers: Claim year and car age group to support dynamic filtering

Key Insights

  1. Profitable Performance
    Underwriting profit of $92M indicates a strong margin between premiums collected and claims paid.

  2. High Claim Rate Among Older Drivers
    The 65+ age group accounted for 45% of average claims per year, far outpacing younger groups.

  3. Regional Disparities
    Southern U.S. states (e.g., Mississippi) showed significantly higher claim rates. This can inform geographic risk pricing.

  4. Vehicle Make Trends
    Nissan and Kia had higher total claims and claims paid percentages. These insights can support pricing adjustments by make/model.

  5. Balanced Gender/Marital Distribution
    Gender and marital status did not show significant deviations in claim ratios, but minor differences exist in claim profiles.

Interactivity Features

  • Slicers for "Claim Year" and "Car Age Group" allow users to dynamically adjust their view.

  • Responsive filters and card visuals auto-update based on selections, making the report fully exploratory.

Conclusion and Recommendations

This Power BI dashboard transforms complex insurance data into clear, actionable insights for analysts, underwriters, and managers. Some strategic actions to consider:

  • Review underwriting criteria for high-claim makes and senior drivers.

  • Investigate causes of regional disparities and align policy pricing.

  • Track underwriting performance annually using dynamic KPIs.