New Enterprise DNA Initiatives

Power BI Challenge 6 - Insurance Complaints Entry from Alvi

Here’s Alvi’s entry for Power BI Challenge 6. @Alvi, would you like to share how you built this dashboard and what your inspiration is in building it?









Here is the link to the report:

To learn about the real-life scenario presented for the challenge, be sure to click on the image below.



I think this challenge was by far the most demanding in terms of deciding what to put in the report as most of the data was dimensional (a lot of dimensions!) and very few measures to play with, infact the number of complaints, total reimbursements and days taken to resolve these complaints were the only measures I could find in the data

To start with the problem statement of the challenge, I just focused to keep the pre-audit report focused on the financial year 2018-19 and 2019-20 so that the user just focuses on the data from a financial year’s perspective (most financial and administrative audits focus on financial year instead of the calendar year). FY was put as a slicer on most pages of the report to keep the filtering simple and focused on that aspect.

The next important aspect was the complaint itself, which could be either open or closed. I simply added a calculated column to label each entry in the ‘complaints data’ table as open or closed. This field was used to filter the report pages and segregate the complaint status analysis.

For the complaint status analysis part, the most challenging bit was to compute the number of days between status changes. I got help from this video of @sam.mckay that explains the process in a very simple manner.

For ‘closed’ complaints, the number of complaints and average days to resolve the complaints are both helpful to give a perspective to the end user of the report about the overall complaint handling process. Each complaint can be further drilled down to see how the process changed states from ‘NEW’ to ‘CLOSED’. During this process, I found a few discrepancies in the data where the ‘complaint date’ and ‘completion date’ mismatches with the data in the ‘Status History Data’ table. I am copying just 2 instances as examples

complaints data

In the snapshot from the ‘Complaints Data’ table the complaint date is 20-08-2018 and the completion date is 22-10-2018, whereas in the screen shot from ‘Status History Data’ (below) these dates do not match with the status, so the duration to resolve the complaint comes out different.

complaints status

Similarly, in another example one of the dates matches and the other date is different between the two tables.

complaints data 2

complaint history 2

I could find this for many other entries as well. Maybe there is some confusion in understanding of this aspect on my end or the data entries are incorrect, somebody can further comment on that. I have used the ‘Complaints Data’ table to compute the ‘days to resolve’ and ‘duration b/w stages’ has been calculated using the ‘Status History’ table.

For customer satisfaction, ‘client satisfaction’ column has been used as a filter to see the satisfaction across regions, product hierarchy and the ‘reason for complaint’ dimensions. It is very hard to say what dimension the end user would prefer to gauge the customer satisfaction.

Similarly, the broker performance has been broken down against the number of complaints, time taken to resolve and the reimbursements measures so the end user can decide which of these dimensions to use for making a decision.

The report design has been inspired by a ‘menu driven’ style, where each of the options is expanded to see the details. I have tried to keep it simple and less cluttered so that the end user can use self-service BI, with minimum slicers, to navigate the report.

I am a bit divided on putting findings in the report, both as comments on each page or separately as a different page, partly because I feel Power BI enables and empowers self-service BI and the end user should be given the freedom to navigate the report in an unbiased manner, but I would love to hear the opinion of other forum members on this aspect as these findings can be easily fitted in the report. I have seen a few videos and read a few columns and the opinion of most experts is divided, especially when the report is being presented to an audience vs when it is shared with different stakeholders. I would love to hear the opinion of others for a better understanding.

I enjoyed working on the report and it was by far the most time consuming challenge, although I started a bit late thinking that it would not be that complex. I am super impressed with all the other submissions and the report by @sam.mckay is perhaps the most detailed I have seen in any of his previous showcase reports.

I hope to see some feedback on my submission and best wishes to the challenge winner!

Abu Bakar Alvi

1 Like

Hi @Alvi, thanks for your detailed description.

Keep an eye on the Challenge wrap-up.

Best of luck!

  • Enterprise DNA Team

I really like your use of color here - very simple shading in the background, so visuals do not seem to ‘fade’ to the back. Consistent use of icons is also a very nice touch. And the cover page/bookmarking leads the user nicely through the report.

The use of a drill-through button to invite users to get more detail strikes exactly the right tone.

I would have liked to see some type of graphic on the Open Complaints Details (perhaps showing current status of those complaints?) But that is because with my users I seem to need at least one chart on every page of a report - unless it is a drill-through detail page. :slight_smile:


Thanks for the comment @Heather

You are right about putting a visualization on the Open Complaints page. I thought of putting some cards to show the number of complaints at different stages but then I dropped the idea as it would have been too many numbers on the page. I could not think of any visual that could have added any value and then I just ran out of time :slight_smile:.

Thanks for the feedback

1 Like

Wow I really love the menu type design that you have embedded into the navigation. That’s so innovative, and has given me some great ideas.

It’s just amazing what some power BI has enabled with a lot of their recent feature updates, especially around bookmarks and also tips etc.

I think this is a really clean and effective looking design for a report. There is nowhere where there feels like information overload, it’s easy on the eye, easy to see the insights that you need and want. Just a super consumer experience.

I also love the way you incorporated the drill down, with the table and the button down the bottom. It’s almost like a website experience Which is perfect for the online service.

Really enjoyed reading through your write-up as well. It’s great to get an understanding of how use thought about the challenge, and how you thought through some of the insights you wanted to showcase.

I think that’s one of the great things about the challenge initiative, is that it’s getting us to think through data that we are not familiar with. It’s allowing us to think outside our own biases we may have built-up around data we use day in and day out.

Super work here, really impressed. Loving seeing the improvement over time.



Thanks @sam.mckay for the feedback and appreciation