Forum Discussion
Insurance Earned Premium/Loss Ratio Calculation
Hi Dale,
I have been thinking about this, and I finally understood that my data model was wrong.
Now the model has the info "flat" month by month, and I let Power Bi do just aggregations.
With this model now I can see the Earned Premium month by month, and calculate the Loss Ratio change month by month.
Thank you Dale for your help!!
Gus.
Hi Jarek,
I really don't think this is the best solution, but here's the file. --> https://drive.google.com/file/d/1rPvkPq14VYiTm3kGK0nbvmFvdD6c36D_/view?usp=sharing
The reason why I say this is becuase I'm creating a record for each policy/risk/coverage/month.
So, for 1 annual policy with 1 risk and 1 coverage, you will have 12 records, all calculated as the last day of the month.
I did it this way because I can't find the correct way of calculating the things I need, like the earned/unearned premium for the loss ratio, and because we don't have a lot of policies.
The good thing about this design, is that I don't have to do complex calculations, I leverage the out of the box functionalities of Power Bi to run simple aggregations (like sum, avg, count, etc). The more complex calculations you add, the slower your dashboard becomes.
But I will reach the day when the data will be too much data.
All data has been scrambled, so if you find James Bond as a client let me tell you, we don't insured James Bond. High risk and all...
If you have any question, please, let me know.
Gus
Hi Gus,
thanks a lot for quick response.
I downloaded the model and will analyze it for my purposes (mainly extended warranty, PA, Travel).
Dashboards looks quiet well.
I was looking any documents/examples in PBI connected with insurance business, but there is not to much in the web 😞
"...we don't insured James Bond. High risk and all..." - but You know, premium could be high. Especially at the end of the year to meet the budget goals 🙂
Br,
Jarek