Session 07: Power BI for Insurance Analytics

1. Power BI vs. Python Visualization

Power BI and Python are complementary tools for insurance data visualization. Each has strengths and weaknesses, and knowing which to use for which task is a mark of a mature insurance analytics professional. The answer is rarely "always use X" — it depends on the audience, the complexity of the analysis, and the need for interactivity vs. reproducibility.

1.1 The Decision Framework

Use this simple three-question framework when deciding between Power BI and Python:

1. Does the audience need interactivity? (slicers, drill-down, cross-filtering) → If yes, use Power BI. If a static chart is sufficient, Python may be faster.
2. Is this a recurring dashboard or a one-off analysis? → If recurring (monthly board report, weekly claims monitoring), invest in Power BI. If one-off (a single chart for a paper or presentation), use Python.
3. Does the analysis require statistical transformations? (smoothing, clustering, regression residuals, distribution fitting) → If yes, Python. If the analysis is aggregations and comparisons, Power BI.

The best insurance analytics teams use both — Power BI for the interactive dashboards that business teams consume daily, and Python for the ad-hoc analysis and model development that feeds into those dashboards.

2. Power BI Interface & Data Loading

This section assumes you have Power BI Desktop installed (free download from Microsoft), and you have the cleaned dataset (`insurance_cleaned.csv`) from Session 05.

2.1 Initial Setup and Data Import

Step 1 — Open Power BI Desktop: You will see a blank canvas with three tabs at the bottom: Report (where you build visuals), Data (where you view tables), and Model (where you manage relationships).

Step 2 — Import the CSV: Click "Get Data" → "Text/CSV" → navigate to `insurance_cleaned.csv` → Click "Load" (not "Transform Data" yet — we will use Power Query for transformations in a moment).

Step 3 — Check the imported data: Go to the "Data" tab (the table icon on the left). You should see all columns from the CSV. Click on columns to verify data types — dates should show as Date, numbers as Decimal Number or Whole Number, and text as Text.

Step 4 — Open Power Query Editor for data transformations: "Home" tab → "Transform Data" → "Transform Data" (opens Power Query Editor). Perform these checks:

• Verify that `claim_date`, `settlement_date`, `start_date`, `end_date` are of type Date (not Text)
• If any date column was imported as Text: click the column header, go to "Transform" → "Date" → select the correct format
• Check that `premium`, `claim_amount`, `sum_assured` are Decimal Number (not Whole Number — premiums need decimals)
• If needed: Use "Replace Values" to clean any stray characters

Step 5 — Review the Model: Go to the "Model" tab (the bar chart icon on the left sidebar). If you imported a single table, you will see only one entity. For this dashboard, a single flat table is sufficient — we flattened the schema in Session 05.

2.2 Creating a Date Table

A date table is essential for time-based DAX calculations. Power BI works best when you have a separate date table linked to your data:

Option A — Create in Power Query: In Power Query Editor, right-click in the Queries pane → "New Blank Query" → Advanced Editor → paste:

let
    StartDate = #date(2020, 1, 1),
    EndDate = #date(2026, 12, 31),
    NumberOfDays = Duration.Days(EndDate - StartDate),
    DateList = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),
    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing()),
    RenamedColumns = Table.RenameColumns(TableFromList,{{"Column1", "Date"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"Date", type date}}),
    AddedYear = Table.AddColumn(ChangedType, "Year", each Date.Year([Date]), Int64.Type),
    AddedMonth = Table.AddColumn(AddedYear, "Month", each Date.Month([Date]), Int64.Type),
    AddedMonthName = Table.AddColumn(AddedMonth, "MonthName", each Date.MonthName([Date]), type text),
    AddedQuarter = Table.AddColumn(AddedMonthName, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    AddedYearMonth = Table.AddColumn(AddedQuarter, "YearMonth", each Date.ToText([Date], "yyyy-MM"), type text)
in
    AddedYearMonth

Then, create the relationship: In the Model view, connect the Date table's `Date` column to your fact table's `claim_date` column. This allows you to use time-intelligence functions in DAX.

3. DAX Measures for Insurance KPIs

DAX (Data Analysis Expressions) is the formula language of Power BI. Unlike Excel formulas that operate on individual cells, DAX measures operate on context — they automatically recalculate based on the filters, slicers, and cross-filtering applied by the user. This is what makes Power BI interactive.

3.1 Foundational Measures

Create these measures in the "Modeling" tab → "New Measure":

-- Total Premium (sum of all premium values in the current filter context)
Total Premium = SUM('insurance_cleaned'[premium])

-- Total Claims Amount
Total Claims = SUM('insurance_cleaned'[claim_amount])

-- Claim Count (using COUNTROWS instead of COUNT — more reliable with column filters)
Claim Count = COUNTROWS('insurance_cleaned')

-- Policy Count (distinct count, since one policy may have multiple claims)
Policy Count = DISTINCTCOUNT('insurance_cleaned'[policy_id])

-- Average Claim Size
Avg Claim Size = DIVIDE([Total Claims], [Claim Count])

3.2 Insurance-Specific Measures

-- Loss Ratio: Claims / Premium
Loss Ratio = DIVIDE([Total Claims], [Total Premium], 0)

-- Format: Home tab → Percentage, 2 decimal places

-- Loss Ratio (as percentage expressed as number 0-100 for gauge charts)
Loss Ratio % = DIVIDE([Total Claims], [Total Premium], 0) * 100

-- Claim Settlement Ratio: Settled / (Settled + Rejected)
-- First, define measures for each status
Settled Claims = CALCULATE([Claim Count], 'insurance_cleaned'[status] = "Settled")
Rejected Claims = CALCULATE([Claim Count], 'insurance_cleaned'[status] = "Rejected")
Pending Claims = CALCULATE([Claim Count], 'insurance_cleaned'[status] = "Pending")

Claim Settlement Ratio = DIVIDE([Settled Claims], [Settled Claims] + [Rejected Claims], 0)

-- Average Days to Settle
Avg Days to Settle = AVERAGE('insurance_cleaned'[days_to_settle])

-- Fraud Rate
Fraud Count = CALCULATE([Claim Count], 'insurance_cleaned'[fraud_flag] = 1)
Fraud Rate = DIVIDE([Fraud Count], [Claim Count], 0)

3.3 Time Intelligence Measures

-- Total Premium Previous Year (requires date table and relationship)
Premium PY = CALCULATE([Total Premium], SAMEPERIODLASTYEAR('DateTable'[Date]))

-- Premium Year-over-Year Growth
Premium YoY % = DIVIDE([Total Premium] - [Premium PY], [Premium PY], 0)

-- Claims Year-to-Date
Claims YTD = TOTALYTD([Total Claims], 'DateTable'[Date])

-- Claims Previous Month
Claims PM = CALCULATE([Total Claims], PREVIOUSMONTH('DateTable'[Date]))

-- Rolling 12-Month Claims Average
Claims Rolling 12M = AVERAGEX(
    DATESINPERIOD('DateTable'[Date], LASTDATE('DateTable'[Date]), -12, MONTH),
    [Total Claims]
)

3.4 Conditional / Categorical Measures

-- Loss Ratio by Product Type (this is implied by context — DAX is automatic)

-- Severity Bucket: classify claims for visual grouping
Claim Severity Bucket = SWITCH(
    TRUE(),
    'insurance_cleaned'[claim_amount] <= 25000, "Low (≤₹25K)",
    'insurance_cleaned'[claim_amount] <= 100000, "Medium (₹25K-₹1L)",
    'insurance_cleaned'[claim_amount] <= 500000, "High (₹1L-₹5L)",
    "Catastrophic (>₹5L)"
)

-- Premium per Policy (average premium per distinct policy)
Premium per Policy = DIVIDE([Total Premium], [Policy Count], 0)

-- Loss Ratio Trend Indicator
Loss Ratio Trend = IF(
    [Loss Ratio] > 0.75,
    "High — Investigate",
    IF([Loss Ratio] > 0.60, "Moderate — Monitor", "Healthy")
)

4. Building Visuals in Power BI

With measures defined, the next step is to build the visual elements. Power BI provides a rich set of visual types. For insurance dashboards, five visual types cover 90% of needs.

4.1 The Five Essential Visual Types for Insurance

5. Page 1 — Claims Overview Dashboard

The Claims Overview page answers the question: "What is happening with our claims?" This is the page that the Head of Claims, the Chief Operating Officer, and the Underwriting team will look at daily. It focuses on five things: volume, speed, cost, settlement rate, and fraud.

5.1 Page Layout

┌─────────────────────────────────────────────────────────────────────┐
│ CLAIMS OVERVIEW  [Slicers: Year ▼] [Product ▼] [Status ▼]          │
├──────────────────┬──────────────────┬──────────────────┬─────────────┤
│ Total Claims     │ Avg Claim Size   │ Avg Days to      │ Claim       │
│ ₹X,XX,XX,XXX     │ ₹XX,XXX         │ Settle XX days   │ Settlement  │
│ vs PY: +X.X%     │ vs PY: +X.X%    │ vs PY: -X days   │ Ratio X.X%  │
├──────────────────┼──────────────────┴──────────────────┴─────────────┤
│ Monthly Claims Trend (Line + Column combo)                          │
│ [Column: claim count by month] [Line: rolling 3-month avg]         │
│ X-axis: Month-Year, Y1: Claim Count, Y2: Avg Claim Size            │
├─────────────────────┬──────────────────────┬────────────────────────┤
│ Claims by Type (Bar)│ Claims by Status     │ Claims by Product     │
│ [X: claim_type]     │ (Donut/Pie)          │ (100% Stacked Bar)    │
│ [Y: claim count]    │ [Settled : Pending   │ [X: product]          │
│                     │  : Rejected]         │ [Y: % of total]       │
├─────────────────────┴──────────────────────┴────────────────────────┤
│ Claims Detail Table — last 100 claims (matrix/table)               │
│ [Claim ID, Customer, Type, Amount, Status, Days Open, Fraud Flag]  │
│ Conditional formatting: Red for >90 days, Fraud flag highlighted   │
└─────────────────────────────────────────────────────────────────────┘

5.2 Building the Page Step-by-Step

Step 1 — Add slicers: Add a "Year" slicer using the Date table's Year column. Set it to "List" or "Dropdown" style. Add a "Policy Type" slicer using the policy_type column. Set to "Tile" or "Dropdown" style. Add a "Status" slicer using status column. These three slicers will filter everything on the page.

Step 2 — Add KPI cards: Create 4 KPI visual elements at the top:

• Card 1: Total Claims (formatted as ₹ Crores with 2 decimal places)
• Card 2: Avg Claim Size (formatted as ₹ with no decimals)
• Card 3: Avg Days to Settle (formatted as whole number, add a goal of 15)
• Card 4: Claim Settlement Ratio (formatted as %, add a goal of 95%)

For each card, enable the "Category Label" to show the measure name. Set background color to a dark card with white text for visual separation from the chart area.

Step 3 — Monthly claims trend chart: Add a "Line and Stacked Column" chart. Column values: Claim Count (Y-axis). Line values: Avg Claim Size (Secondary Y-axis). X-axis: Date table's YearMonth or Date field. Add a "rolling average" if you created a measure for it. Format the line in a contrasting color (red or orange against blue columns). Remove the chart title and add it as a text box above: "Claims Volume and Average Size Over Time."

Step 4 — Claims by type bar chart: Add a clustered bar chart. Axis: claim_type (from insurance_cleaned). Value: Claim Count. Sort descending by value. Enable data labels. Apply consistent color to match the dashboard theme.

Step 5 — Claims by status donut: Add a donut chart. Legend: status. Value: Claim Count. Add data labels showing percentage and count. The donut should immediately answer: what proportion of claims are settled vs. pending vs. rejected?

Step 6 — Claims by product stacked bar: Add a 100% stacked bar chart. Axis: policy_type. Value: Claim Count. Legend: status. This shows the proportion of settled/pending/rejected claims for each product type, allowing the claims manager to see if one product is generating more pending or rejected cases.

Step 7 — Claims detail table: Add a table visual showing: claim_id, customer_id, policy_type, claim_amount, claim_date, days_to_settle, status, fraud_flag. Apply conditional formatting to days_to_settle (red background if > 90 days). Apply conditional formatting to fraud_flag (yellow background if 1). This table gives the operational team a working list of claims to manage.

Step 8 — Arrange and align: Use Power BI's format tools to align all visuals evenly. A 16:9 page ratio works well. Set the page background to a dark or light color that matches your chosen theme. Ensure all visuals have the same border styling for a professional, cohesive appearance.

6. Page 2 — Policy Analytics Dashboard

The Policy Analytics page answers the question: "What is happening with our portfolio?" This is the page for the Head of Products, the Chief Marketing Officer, and the Distribution team.

6.1 Page Layout

┌─────────────────────────────────────────────────────────────────────┐
│ POLICY ANALYTICS  [Slicers: Year ▼] [Product ▼] [Channel ▼]        │
├──────────────────┬──────────────────┬──────────────────┬─────────────┤
│ Total Premium   │ Policy Count     │ Avg Premium      │ Loss Ratio  │
│ ₹X,XX,XX,XXX    │ XX,XXX          │ ₹XX,XXX          │ XX.X%       │
│ vs PY: +X.X%    │ vs PY: +X.X%    │ vs PY: +X.X%     │ vs PY: ±X%  │
├──────────────────┴──────────────────┴──────────────────┴─────────────┤
│ Premium & Loss Ratio Trend (Line and Stacked Column — Dual Axis)   │
│ [Column: Total Premium by month] [Line: Loss Ratio trend]          │
│ [Reference line on Loss Ratio at 75%]                              │
├──────────────────────────┬──────────────────────────────────────────┤
│ Premium by Product       │ Premium by Channel & Product            │
│ (Horizontal Bar)         │ (Stacked Bar, Channel axis, Legend=Prod)│
│ [Sorted descending,      │ [Data labels for total per channel]     │
│  data labels in ₹Cr]     │                                          │
├──────────────────────────┴──────────────────────────────────────────┤
│                          │                                          │
│ Policy Portfolio Matrix — Premium vs. Loss Ratio by Product        │
│ (Scatter chart: X=Premium, Y=Loss Ratio, Legend=Product,           │
│  Size=Policy Count)                                                │
│ Quadrant lines at median premium and 75% loss ratio                │
│ 4 quadrants: High Premium/Low LR (⭐) | High Premium/High LR (⚠)   │
│             Low Premium/Low LR (✓)  | Low Premium/High LR (✗)     │
└─────────────────────────────────────────────────────────────────────┘

6.2 Building the Page Step-by-Step

Step 1 — Top slicers: Add slicers for Year, Policy Type, and Channel. Use dropdown style for compact layout. These slicers control all visuals on the page.

Step 2 — KPI cards: Total Premium, Policy Count, Avg Premium per Policy, and Loss Ratio. Use the KPI visual type where possible to include the trend indicator (up arrow/down arrow) and goal comparison. Format the Loss Ratio card with conditional formatting on the value — green if below 60%, amber if 60–75%, red if above 75%.

Step 3 — Dual-axis trend chart: Line and stacked column chart. Column values: Total Premium. Line values: Loss Ratio %. Secondary axis for Loss Ratio. Add a reference line on the secondary axis at 75% (use "Analytics" pane → "Line" → Constant Line). This single chart answers the most important portfolio question: "are we growing profitably?"

Step 4 — Premium by product: Horizontal bar chart. Axis: policy_type. Value: Total Premium. Sort descending. Format data labels in ₹ Crores. This chart shows which products are driving premium volume.

Step 5 — Premium by channel and product: Stacked bar chart. Axis: channel. Value: Total Premium. Legend: policy_type. 100% stacking can be used if the focus is on channel composition by product. Regular stacking if channel volume comparison matters.

Step 6 — Premium vs. Loss Ratio scatter: Scatter chart. Values: policy_type (in Legend). X-Axis: Total Premium. Y-Axis: Loss Ratio %. Size: Policy Count. Add constant lines at the median of X and at 75% on Y. The four resulting quadrants show at a glance which products are: high-volume with healthy loss ratios (quadrant to protect), high-volume with concerning loss ratios (quadrant to investigate), low-volume healthy, and low-volume concerning.

8. Dashboard Design Principles

A dashboard that is technically correct but poorly designed will not be used. The following principles are distilled from observing which insurance dashboards actually drive decisions — and which ones sit unused.

8.1 The Five-Second Test

A stakeholder should be able to look at your dashboard and answer the most important question within five seconds. For the Claims Overview page: "Are claims trending better or worse than last month?" For the Policy Analytics page: "Is our premium growth profitable?" If the answer is not visible within 5 seconds, the dashboard needs to be redesigned. The most important number must be the biggest thing on the page, ideally in the top-left position where the eye naturally goes first.

8.2 The Three-Page Rule

An insurance analytics dashboard should have exactly three pages — one for claims, one for policies, one for customers. More pages mean the user never goes past page 2. Fewer pages mean the content is insufficient. Within each page, limit yourself to 6–8 visuals. More than 8 visuals produces visual fatigue; fewer than 4 generally fails to tell a complete story.

8.3 The Consistent Color Rule

Use a consistent color scheme and assign meaning to colors. For insurance dashboards, establish these conventions: Green = good, healthy, improving (low loss ratio, fast settlement, high retention). Amber = monitor, moderate, borderline. Red = concerning, requires action (high loss ratio, long settlement, high fraud). Blue = neutral, informational (volumes, counts, premiums).

Apply these colors consistently across all three pages. A red loss ratio on Page 1 and a red KPI on Page 2 should convey the same level of urgency. Never use red for a neutral measure — overuse of red creates alarm fatigue and the stakeholders will stop taking it seriously.

8.4 The Filter Consistency Rule

When a user selects "Motor" in the Product Type slicer on Page 1, they expect the same filter to apply when they navigate to Page 2 and Page 3. Power BI calls this "synchronizing slicers across pages." Use the "View" → "Sync Slicers" feature to ensure consistent filtering. Nothing frustrates a dashboard user more than having to re-select the same filter on every page.

8.5 The "Why" Annotation Rule

For every chart that shows a significant trend, add a text box or annotation explaining why the trend occurred. A chart showing "Loss Ratio increased from 68% to 82% in June" is incomplete. The annotation "Monsoon claims surge — motor accident frequency up 35%" turns data into insight. In a static report, these annotations are written by the analyst. In a Power BI dashboard, you can use "Text Box" elements pinned in specific locations to provide permanent context for the most common interpretations.

8.6 Publishing and Sharing

Once your dashboard is built, you have three sharing options:

• Power BI Service (cloud): Publish to the Power BI service (requires a Pro license for shared consumption). Schedule daily data refresh. Create a mobile-optimised view for executives.
• Power BI Report Server (on-premises): For insurers with regulatory restrictions on cloud data. Requires Power BI Premium licenses. Slower feature release cycle than cloud.
• Export to PDF/PPT: For stakeholders who do not have Power BI access. Export each page as a high-resolution image or use the "Export to PowerPoint" feature. Create a "print-friendly" version of each page (white background, lighter shading, consistent page breaks).