Power BI Challenge 6 - Insurance Complaints

Challenge 6- Insurance Complaints Data

Is it me or are we getting through these instalments super quick??

So here we have the 6th instalment of the EDNA challenge

Remember its open to everyone so don’t be afraid to get stuck in.

To learn more about the challenge and how it works be sure to check out the forum below!

If you’re not sure of anything just reach out to me.

Remember: The weekly winner will receive a complimentary membership to the platform that they can share with anyone and the opportunity for your work to be showcased across our channels. We have also created categories for the best newcomer and non-member so please get stuck in.

THE BRIEF

You are working at a large insurance company who are due to undergo an internal audit with regards to their complaint’s procedure.

The company are forward thinking and currently have a data warehouse in place alongside several models.

Management have asked you to look at the model and produce insights for management level that can highlight any issues and prepare them for the audit.

Given your recent work they have complete trust in your ability and have given you freedom to present this back as you see fit.

Some factors to consider:

  • The most recent 2 years of data is what most stakeholders are interested in
  • The ability to see status changes and when they happened
  • Complaints broken down by the dimensions
  • Client Satisfaction
  • Worst offending brokers

SUBMISSION DUE DATE - Monday, 7th September 2020 (PST)

Please can you all submit your PBIX files to powerbichallenge@enterprisedna.co

Objective: Use the model to produce a compelling report

The data set can be downloaded below.

Insurance - Complain Administration.xlsx (1.9 MB)

Best of luck!

Haroon

14 Likes
Power BI Challenge 6 - Insurance Complaints Entry from Marieke
Power BI Challenge 6 - Insurance Complaints Entry from Alvi
Power BI Challenge 6 - Insurance Complaints Entry from Diana
Power BI Challenge 6 - Insurance Complaints Entry from Amal (non-member)
Showcase data files
Power BI Challenge 6 - Insurance Complaints Entry from Greg
Power BI Challenge 6 - Insurance Complaints Entry from Mudassir
Power BI Challenge 6 - Insurance Complaints Entry from Daniel
Power BI Challenge 6 - Insurance Complaints Entry from Alex
Power BI Challenge 6 - Insurance Complaints Entry from Mohammed
Power BI Challenge 6 - Insurance Complaints Entry from Zoe
Power BI Challenge 6 - Insurance Complaints Entry from Jarrett
Power BI Challenge 6 - Insurance Complaints Entry from Alejandro (non-member)
Power BI Challenge 6 - Insurance Complaints Entry from Amdi
Power BI Challenge 6 - Insurance Complaints Entry from Brian
Power BI Challenge 6 - Insurance Complaints Entry from Matteo (non-member)
Power BI Challenge 6 - Insurance Complaints Entry from Sunip (Newcomer)
Power BI Challenge 6 - Insurance Complaints Entry from Fatjan (non-member)
Power BI Challenge 6 - Insurance Complaints Entry from Hideo
Power BI Challenge 6 - Insurance Complaints Entry from Eze
Power BI Challenge 6 - Insurance Complaints Entry from Nhat
Power BI Challenge 6 - Insurance Complaints Entry from Vida (non-member)
Power BI Challenge 6 - Insurance Complaints Entry from Nebiyu
Power BI Challenge 6 - Insurance Complaints Entry from Hossein (non-member)
PowerBI Challenges Initial Questions for each of the challenges
Power BI Challenge 6 - Insurance Complaints Entry from Mosbehi (non-member)

Here we go :+1:

2 Likes

Exciting, thanks Haroon

1 Like

I’m not sure if everyone checked the full excel file, there is other worksheets within the file.

I thought the same thing @BrianJ

thank Keith

2 Likes

@Keith,

Thanks! When I opened the file, only the last two tabs showed - but scrolling left revealed the rest of the worksheets.

doh

  • Brian
4 Likes

@BrianJ LOL

I thought the same thing when I opened the file and missing something and then realized that there was more worksheets.

welcome :smiley:
Keith

2 Likes

The way i found out was to perform few countif formulas in excel and it took quite a long time to process so I look under the hood and find all those critters (tables) were nesting in the carburetor :slight_smile:

3 Likes

This looks great Haroon! I am excited to see some of the results and get involved in these challenges :slight_smile:

3 Likes

@haroonali1000
What is “ExpectedReimbursement” - can you please explain this?

Hi @Mohammed_ali,

ExpectedReimbursement=Monetary Value (£) associated with the claim.

Regards,
Haroon

Sorry but I am a little confused. I believe the main “fact” table to be the complains data but what is the Status History Data?

I beleive the Status History Data is the main fact table , like that?

1 Like

@haroonali1000
I don’t see any Administrator Dimensions tab. There is Administrator ID in Complaints data and not sure to which table we need to join.

1 Like

We have been given Regions tab but in Complains Data there is no column that can link to Regions table.

@simrantuli29 Regions -> Customers -> Complaints

Hi haroonali1000,

I just found that some complaints that status is abnormal but I’m not sure, so could you help us to explain some questions related to the complaint status as below. :smiley:

  • What is the initial status of the complaint?
  • Why don’t we have the status ID 5 in the data history?
  • Could you tell us a little bit about the process related to the complaint status?

Thanks & Best Regards
Nhat Lam

@AntrikshSharma,

What fields are you joining on to create those relationships? I’m having the same problem as @simrantuli29, since the ID field in Region doesn’t seem to join properly to either Customers or Complaints.

  • Brian

From Regions Table “ID” to Customers Table"RegionID"… Then joining customers table and complaints table with “CustomerID”.

@BrianJ

Here is what I have done so far:
Insurance.pbix (918.9 KB)
Also I changed Column names here and there so my version might break in your PQ view
Insurance - Complain Administration.xlsx (2.0 MB)

@MudassirAli,

:+1:. Totally missed that RegionID field. Thanks!

  • Brian
1 Like