Power BI Challenge 6 - Insurance Complaints

[haroonali1000]

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!

About the Challenges category

Welcome to the EDNA data challenge. An opportunity to enhance your Power BI development and learning curve. Sharing, learning and growing! Join us bi-weekly for a new scenario based on what you are likely to face in the real world. We take a data set and turn it into an actionable report providing information and insight. The Enterprise DNA community will then come together to review the project and would love for you to join us. So How Does It Work? Join the EDNA Challenge an awesome com…

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.

Newcomer Prize for Data Challenge 5

All, Shout out to @bafuzie and others submitting first-time entries to this Data Challenge. I really believe that regardless of your experience level these challenges are among the best ways possible to quickly build your Power BI skills. To try to encourage more members early in their Power BI journey to participate, I am offering the following choice of prizes to the top Newcomer entry, as judged by @haroonali1000 and the Enterprise DNA Team: a copy of the Definitive Guide to DAX, 2nd Ed…

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

[MudassirAli]

Here we go

[sam.mckay]

Exciting, thanks Haroon

[Keith]

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

[BrianJ]

@Keith,

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

• Brian

[Keith]

@BrianJ LOL

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

welcome
Keith

[hamidmilani]

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

[rhyschappel]

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

[Mohammed_ali]

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

[haroonali1000]

Hi @Mohammed_ali,

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

Regards,
Haroon

[mbraun]

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?

[Marta]

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

[sunip]

@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.

[simrantuli29]

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

[AntrikshSharma]

@simrantuli29 Regions -> Customers -> Complaints

[NhatLam]

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.

• 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

[BrianJ]

@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

[MudassirAli]

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

[AntrikshSharma]

@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)

[BrianJ]

@MudassirAli,

. Totally missed that RegionID field. Thanks!

• Brian