Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jazz_MT
Frequent Visitor

Trying to calculate loss ratio but not working

Hi All,

 

Hope you can shed some light on what I am doing wrong as I am using PowerBI for my Masters Project.

 

I am trying to calculate the loss ratio which has the formula (Total Claims incurred to Date/Total Premiums earned to date)*100. 

I have calculated both the Total Claims Incurred and Total Premiums Earned using the Year-to-Date Time Intelligence quick measure function. The value of 2017 is correct however the values of 2016 and 2015 are incorrect and also when the graph is plotted as shown in the attached image below. Whats strange is the Graphs plotting the Total Claims incurred and Total Premiums incurred is correct and I was assuming this would be a simple division.

 

Really hope someone can help me with this as it would be much appreciated 🙂 

 

Thanks

Capture.PNG

 

 

3 REPLIES 3
Interkoubess
Solution Sage
Solution Sage

Hi @Jazz_MT,

 

Do you have a calendar table ?

 

please could you show a sample of your data ( how it is stored) and will try to help.

 

Thx.

Hi @Interkoubess

 

I do not have a calendar table and below are screenshots of my data model including the tables. Hope you can help me on this. If you need further details maybe we could skype ? 

 

Really appreciate your help

 

Data modelData modelClaims 1Claims 1Claims 2Claims 2Earned PremiumEarned PremiumPolicy HeaderPolicy HeaderPolicy LinesPolicy Lines

Hi @Jazz_MT,

 

Could you please post your data in the text mode due to you can share your data? Sharing a PBIX file would be great if it's convenient for you. Dummy data is enough. Maybe you could check these things below first.

1. It's highly recommended using time intelligence functions with a date table.

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNumber", MONTH ( [Date] )
)

2. Year-to-date functions accumulate the result. Is this what you want?

3. Could you please post the formulas you have created?

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.