Power BI Challenge 6 - Insurance Complaints Entry from Mudassir

Here’s Mudassir’s entry for Power BI Challenge 6. @MudassirAli, feel free to add other details of your work.

Here is the link to the report:

And here’s how Mudassir described it:

I took the approach like the audit reports and tried to minimize the use of slicers while communicating the required task.

  1. In 1st page, I have included a general overview about the company also showing in scatter graph the company’s # of customers in relation to the total population.
  2. Here I have shown the complaints by years, region and by other dimensions.
  3. The same report is used to identify the number of days taken to handle the complaints.
  4. The complaints status is shown in this page and the time taken from one instance to other instance can be seen via tooltip. However, tooltips are taking forever to load so I have given up on this.
  5. Clients satisfaction is shown and have tried to identify that company’s low Not satisfied rate is due to the absence of data therefore, satisfaction rate is not giving the accurate customer satisfaction.
  6. Here I have shown brokers performance by the avg time taken by brokers to resolve the complaints. They are also grouped into different categories to identify performances in groups. I have added the WHAT IF parameters to dynamically select Worst brokers performances.
  7. In the last page have summarized the findings and the steps to be taken before the internal audit.

Clear filter button is at the top right corner of the pages.
Navigation through pages is by clicking on the headings of the pages.

This challenge made me work the hardest. I have just tried to give the idea as to how to use the report and the detailed write-up will be done later.

When I saw the details of the challenge I knew how my report is going to look like but the difficult part was to actually make it work because there were many details in the data set. The report was meant to help prepare the management for the audit and more often than not management wants a summary of issues rather preferring to slice/dice the details to get the results. Therefore, decided to prepare the report that can be easily understood by management with self-explanatory visuals.

The 70% of the time was spent in figuring out the design and flow of the report and 30% was actually spent in completing it. The report does not have fancy navigation and bookmarks but the creative organization of visuals for the story-telling. Here is how I prepared the report:

Home Page:
I made use of the following online website for the report template:

https://www.canva.com/

However, I used the combination of three to four different reports to design the home page. At home page, a brief description is given about what to expect in the report and from the table of contents, it is possible to navigate to different pages by clicking on the headings.

01-General Overview:

On the very first page of the report, a general summary of the organization is given to the end user i.e. the number of clients, complaints, brokers, expected reimbursements, average time taken to resolve the complaints and clients’ satisfaction level.

Line and stacked column charts show the number of complaints by day also pointing out on a card about the number of Urgent & Non-Urgent complaints. This gives the idea that almost every time the brokers have good amount of time to resolve the complaints and get clients’ satisfaction as the complaints are non-urgent.

Scatter Chart shows the company’s clients compared with the population in different states to give the estimate of the market share.

At the bottom, cards show the % of customers by Gender.

02-Complaints Distribution

Here is how it got tricky as I wanted to highlight the products and other elements where the % of complaints are higher without stuffing the report with irrelevant details. Instead of using the slicers, I made use of the clustered bar chart as it gives the good visibility of % of complaints.

The matrix heat map table contains the yearly % of complaints by regions, surrounded by :
-clustered column chart that highlights the complaints by month
-clustered bar chart that highlights complaints by region
-Line & stacked column chart is placed next to the above chart to show the complaints trend by year
-Cards are placed atop of heat maps to give the % of complaints by year.

Map is also used to highlight the worst affected areas in terms of complaints.

At the bottom of the page , tree map is used instead of the horizontal slicer because conditional formatting is allowed in tree map and the dark color shows the highest % of complaints by region.

To go back to the Home Page, click on the heading at the top.

03-Average Complaints Handling Days

The same technique has been used as in page 02-Complaints Distribution with the only difference that the % of complaints is replaced by the average handling of complaints days.

04-Complaints Status

The number of complaints segmented by status is shown at the right side of the page including the average days the status takes.

The line chart also shows the average days taken in each complaints processing stage where In-Progress status takes the highest amount of time.

At the bottom table shows the detail of the complaints with status and a tool tip is also added so the status for each complaint can be seen.

05-Clients Satisfaction Survey

The card visual shows the number of clients satisfaction survey broken down into three components i.e. Satisfied, Not Satisfied & Data N/A.

Area chart is also used to highlight the highest number of surveys by days.

Just below the area chart, line & stacked column chart is used to highlight the surveys for each product category in days and it can be easily be seen when the highest number of surveys was conducted in general and also by each product category.

With each product category, stacked bar chart is also used to give the total of surveys for each product category.

Here the issue is not about the # of dissatisfied clients as the % is very small but the number of surveys not obtained. It is possible that those customers were dissatisfied and didn’t bother to go ahead and take part in the survey.

06-Brokers Performance Measurement

Here I made use of the scatter chart with technique discussed by @sam.mckay:

The brokers are grouped into Poor, Average, Best and Top Category based on number of days taken to resolve the complaints. Customers dissatisfaction number was not used to measure the performance because firstly, the dissatisfaction % was not significant and secondly it is implied that the customers will be dissatisfied if the length of time taken to resolve the complaints is higher.
With the grouping, the company can pick both the poor and top performers and analyse what each group segment is doing to resolve the complaints

Clustered bar charts are used side by side to highlight the expected reimbursements and average days taken to resolve the complaints by brokers. I multiplied the expected reimbursements with -1 so I could show the chart from right to left for comparison. Moreover, I used the What-If-Parameter to dynamically select the number of brokers.

The info icon is also placed if the end user wants to extract all the brokers. The total number of brokers can be inserted in TopN slicer to see all the brokers.

07-Findings

In the end, summary of findings and recommendations are highlighted to management so the relevant action(s) can be taken before the internal audit.

To navigate to the Home Page, click on the Findings Heading.

Dealing With Status History Table

This was the complicated part as there were multiple dates for the same status:

So to cater this problem, I used the Power Query instead of DAX formula. I created a duplicate table and used the Group By function to extract the earliest date of a particular status:

Then I created a Merged Column with ComplaintID and Complaint Status ID to create a unique column and then removed the duplicates:

Dealing With Blanks in Card Visual
It’s not appealing for the end user when a Card Visual shows blank when there is no data for that item/category:

Therefore, I used a quick fix to cater this problem by adding +0 at the end of a measure so the field shows 0 when there is no data:

However, there is only one caveat for this fix and it’s that it only works well in a Card Visual not in table or other visuals.
*- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *

All in all it was a very good challenge because the data set was close to the actual data stored by sophisticated ERP systems.

I learned the hard way that Good Relationships are also important in Power BI.

Thank you EDNA team members for making this happen.

3 Likes

The way you have presented your charts, they invite the user (without giving specific direction) to click on them for further analysis. The matrix heat maps are very clever, and if you hadn’t left the expansion buttons turned on, I would have been asking what visual was used. :+1:

I like your choice of visuals on the Brokers page - scatter charts are so rarely used, and this seems like a great use case.

The line charts on at the bottom of that page did mess with me a little bit - when I first looked at it, I expected the category axis to be aligned ( so that Ryan Black on the highest days taken would be matched to Ryan Black on the expected reimbursements). This took a second to realize why I had made that assumption and re-evaluate. I think it is because of the ‘funnel’ appearance of the two charts together.
However, it has a very nice aesthetic appearance, so depending on the audience, I can see this working.

One caution if you are going to invest this heavily into a color scheme for your report - consider formatting the visual header icons (or turning them off) - the white really stands out when mousing over the various parts of the report. (The black tooltip does as well, but it does not seem as stark a contrast)
image

1 Like

@Heather Thanks for your comments and for evaluating the report so closely. Yes you are right about the tool tips. For this report, turning them off would have been way better. I will give white color a try in this report for visual headers.

Thanks Again!

Wow I don’t even know where to really start here because this so much good work embedded into your report.

I can see very quickly you’ve put a lot of effort into the design and consumer experience. And it really comes off. Anyone who was delivered a report like this within an organisation would be wowed and would love reviewing this information on a regular basis.

To me what this highlights the most is that if you can design your report really affectively it is just so much more engaging. That is what is missing in so many reports and you’ve really showcased it in a way here that any stakeholder would be amazed at what they can discover within a dataset.

There is so many innovative things here, starting with the homepage and navigation, also how you’ve highlighted key metrics on the right hand side on some of the beginning pages, also I love the way you’ve designed the colours and the theme that you have used.

Just the entire report is really spectacular. I congratulate you on your efforts here. You’ve inspired me for some follow-up challenges for sure.

I also love the dynamic ranking visualisation showing two bar charts opposing each other. That is seriously cool and really well thought through. It works perfectly for this report.

I really have nothing to advise or add for improvements because to me this is the complete report. Well done and can’t wait to see some future submissions and what you dream up.

Sam

3 Likes

@sam.mckay That’s such big compliments from you. I started using power bi a while ago just because of your videos. Firstly, your videos gave a good background to start with. Secondly and most importantly, the way you designed your dashboards really inspired me. I didn’t know that power bi is such a powerful tool and every learning experience is a satisfying one.I still have a long way to go.

Thank you again for the appreciation.

3 Likes