Creating my first report in Power BI
This short post will discuss the steps I undertook to complete my first Power BI report.
1. A little backstory
As a fresh data science intern, one of my first tasks was to create a report in Power BI. And write a Medium article about it. So, here we are.
I started by finding the data first. My interests are board games, hydroponic farming and definitely not home insurance. Hence, I decided to do my report about a fictitious home insurance company. I looked for datasets on the Office for National Statistics website (ONS), data.gov.uk and Kaggle. Kaggle’s dataset seemed appropriate, as it contained what I thought I needed. It also had some data about police. Which I later understood to have meant insurance ‘policy’. Still, it gave me a little chuckle.
2. The Power BI challenge
I proceeded to load the data into Power BI and clean it. This step took a fair amount of time as a lot of the columns had ambiguous names and I had to change them into something more understandable. Next, I changed many columns’ data types into correct kinds. This was an iterative part of the process as I caught myself doing it throughout the creation of the report. I also removed nearly-empty columns and computed a few new ones. Straight away I was interested in the time elapsed from getting an insurance quote to purchasing said insurance. So, I computed a new column, which calculated this difference in days. The data was not perfect, as many values were negative. If this were real data, I would’ve had to deal with this.
I then proceeded to create some graphs. First were the performance visuals with the number of policies and their value. I added a slicer, which allowed me to adjust the time period for my graphs. I was quite proud of that.
Get Gosia Zurek’s stories in your inbox
Join Medium for free to get updates from this writer.
Next, I looked at factors which can influence real-life insurance and my attention shifted to claims. The insurance companies’ nightmare. Fortunately, I had a column which indicated whether the customer had reported a claim in the last three years. Here, I decided to create my report for a management team looking for ways to improve their quotation system and detect some risks.
3. Description of the report
The report ended up having two clearly separated sections.
The first section had information about the number of policies sold and their overall value in the form of key indicators. This information was also plotted against years in a line chart. A small table next to it depicted a policy breakdown by type; the categories included cancelled, lapsed and live policies. Those visuals were meant to indicate the company’s performance. Within this section, I also put a bar chart showing average time from getting a quote to purchasing a policy. It turned out to be quite significant, around two months, for both men and women. Definitely something that the company should look at and improve.
The second section of the report described some risk trends that I observed in the dataset. First, I created a line chart, plotting average insurance value and percentage of total policies’ count against risk rating. The data showed that the company signs significantly less high-risk policies despite their cost being higher than low-risk policies. This seemed logical and showed that the company was being cautious with their customers.
Additionally, I looked at claims, as their repayments probably takes up a greater amount of the revenue. Thus, I looked at flooding and locks. For flooding, 98% of those who had a claim in the last three years were also in a floor-risk area. This was a huge number and my recommendation would be to closely assess those in flood-likely homes and their policies.
I also looked at locks and 30% of those who have put in a claim had inappropriate locks. A trend not as extreme as the one above, however, still significant. The recommendation here would be to ask potential customers to fit secure locks on their property. Alternatively, as a commercial campaign, free lock fitting could be advertised to customers, as that would probably cost less than repaying the claims.
4. Conclusion
The main take-home message would be to definitely adjust the assessment period between obtaining a quote and purchasing insurance. Also, despite the company not signing a lot of risky policies, more careful consideration of applicants may be beneficial, such as homes with a flooding risk. Alternatively, adjusting risk rating to increase if homes are likely to be flooded may cause more cautiousness when taking on a new client. Assessment of appropriate locks may also save the company from additional claims, or could even be seen as a potential advertisement campaign.
Doing this was a lot of work, even for such a small-scale project. However, I am grateful for the opportunity and without the pressure of the internship, it would have taken me a lot longer to actually sit down and persevere a whole project in Power BI. It is definitely still not as intuitive as I would like it to be, but hopefully at some point it will be.
