Forum Discussion

gguadalupe's avatar
gguadalupe
Frequent Visitor
9 years ago
Solved

Insurance Earned Premium/Loss Ratio Calculation

Hi All! I'm new to PowerBi and I'm trying to implement an insurance dashboard. All is fine, but now I'm trying to implement the loss ratio formula controled by a slicer filled with my Dates table. ...
  • gguadalupe's avatar
    gguadalupe
    9 years ago

    v-jiascu-msft

     

    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.

PowerPaddy's avatar
PowerPaddy
Frequent Visitor
9 years ago

Thanks for posting this. I've been trying to acheive the exact same calculation. Have you had any success in creating a loss triangle in PowerBI? I've been looking for a Loss Triangle custom visualisation but can't find one.

 

Thanks

Patrick

gguadalupe's avatar
gguadalupe
Frequent Visitor
9 years ago

Hi Patrick,

 

With the model I posted is easy to archive a triangle.

 

In table vPowerBiDate, create a column: Month = FORMAT(vPowerBiDate[Date],"MMM YYYY")

And after that another: MonthOrder = FORMAT(vPowerBiDate[Date].[Date],"yyyymm")

Click Month column, click Modeling tab, click Sort By Column and select MonthOrder.

This will allow you to show the Month column with nice format and order by month value.

Do the same with accident date of table vPowerBiClaim.

With a Matrix control, choose vPowerBiDate.Month as columns and vPowerBiClaim.AccidentMonth as Rows.

In Values put the Incurred and you are good to go!

 

Here's an example of the final loss triangle.

 

 

Hope this helps.

 

Gus.

 

  • PowerPaddy's avatar
    PowerPaddy
    Frequent Visitor
    9 years ago

    Extremely helpful, thanks Gus!