Calculate LTIFR & TRIFR for multiple companies

[narragirl85]

Hi Community,

I need to be able to show on a dashboard the calculated LTIFR (Lost Time Injury Frequency Rate) & TRIFR (Total Recordable Injury Frequency Rate) rate for my own company as well as those of our subcontractors.

My company is represented as 'JKL4' and needs to be separate; all other companies should have a combined rate.

I also need this to be on a rolling 12-month basis, so the rate for Jan 2024 will take into account the hours worked and injuries recorded from Feb-23 to Jan-24, the Feb-24 will be from Mar-23 to Feb-24 and so on and so forth

The data is coming from an online SharePoint list that has a lot of columns, but I only need to use six for the calculation

• Month
• Contractor
• Hours
• LTI (Lost Time Injury)
• RDI (Restriced Duty Injury)
• MTI (Medical Treatment Injury)

LTIFR calculation (LTI only)

TRIFR calculation (LTI, RDI, and MTI)

I have included a link to Tb_HSEQ_Stats  the data I will be using. Only the contractor names have been changed; all other data is correct

The parent table name is 'Tb_HSEQ_Stats' 

[v-yiruan-msft]

Hi @narragirl85 ,

I have no permission to access your shared file, could you please grant me the proper permission to access it? Thank you. Before share your file, please exclude the sensitive info in it. 

In addition, please try to create the measures as below and check if they can return the expected result.

1. LTIFR calculation

LTIFR = (SUM('Tb_HSEQ_Stats'[LTI]) * 1,000,000) / SUM('Tb_HSEQ_Stats'[Hours])

2. TRIFR calculation

TRIFR = ((SUM('Tb_HSEQ_Stats'[LTI]) + SUM('Tb_HSEQ_Stats'[RDI]) + SUM('Tb_HSEQ_Stats'[MTI])) * 1,000,000) / SUM('Tb_HSEQ_Stats'[Hours])

3. Filter the company "JKL4"

LTIFR_JKL4 = CALCULATE([LTIFR], 'Tb_HSEQ_Stats'[Contractor] = "JKL4")

4. Get the values which the company is not "JKL4"

LTIFR_Others = CALCULATE([LTIFR], 'Tb_HSEQ_Stats'[Contractor] <> "JKL4")

Best Regards