Power BI - Insurance Complaints Analysis

This project provides an analysis for a large insurance company who are due to undergo an internal audit with regards to their complaint’s procedure.

See bottom of page for additional images, including model view.

Total Complaints

Total Complaints = COUNTROWS( VALUES( ComplainsData[ID]))

Complaints with satisfied customers

Complaints with Satisfied Customers = 
CALCULATE( [Total Complaints],
    FILTER(ComplainsData, ComplainsData[Satisfaction Level] = "satisfied"))

Urgent complaints

Total Urgent Complaints = 
COALESCE(
CALCULATE( [Total Complaints],
    FILTER(Priorities, Priorities[Description] = "Urgent")),
    0)

Avg days to resolve complaints

Avg Complaints Duration = AVERAGEX( ComplainsData, ComplainsData[Complaint Duration])

Open complaints

Open Complaints = 
COALESCE(
CALCULATE([Total Complaints],
FILTER(Statuses, Statuses[Description] <> "Closed")),
0)

Closed complaints

Closed Complaints = 
COALESCE(
CALCULATE([Total Complaints],
FILTER(Statuses, Statuses[Description] = "Closed")),
0)

% closed complaints

% Closed Complaints = 
DIVIDE( [Closed Complaints], [Total Complaints], 0)

Cumulative complaints

Cumulative Complaints = 
CALCULATE( [Total Complaints],
    FILTER( ALLSELECTED( Dates),
        Dates[Date] <= MAX( ComplainsData[ComplainDate]) ) )

Cumulative complaints (last month)

Cumulative Complaints LM = 
CALCULATE( [Total Complaints Last Month],
    FILTER( ALLSELECTED( Dates),
        Dates[Date] <= MAX( ComplainsData[ComplainDate]) ) )

Total brokers

Total Brokers = 
COUNTROWS(
    VALUES( Brokers[BrokerID]))

% of all broker complaints

% of All Broker Complaints = 
VAR AllComplaints = CALCULATE([Total Complaints], ALL(Brokers[BrokerFullName]))

RETURN
DIVIDE([Total Complaints], AllComplaints, 0)

Top 3 brokers with most complaints

Top 3 Brokers with Most Complaints = 
VAR BrokerRank = VALUES(Brokers[BrokerFullName])

RETURN
CALCULATE( [Total Complaints], 
TOPN(3, 
ALL( Brokers[BrokerFullName]),
[Total Complaints]),
BrokerRank)

Distinct customers who raised complaints

Distinct Customers Who Raised Complaints = 
COUNTROWS(DISTINCT(ComplainsData[CustomerID]))

Distinct % customers who raised complaints*

Here I used distinct customers who raised complaints instead of just total complaints divided by total customers, as it occurred to me that one customer may have raised more than one complaint.

Distinct % Customers Who Raised Complaints = 
    DIVIDE( [Distinct Customers Who Raised Complaints], [Total Customers], 0)