Month over Month Injury Frequency Rates Per Project

[alicefloro-ABI]

Hi!

I've got a powerbi report that I need to create some month-over-month injury frequency line charts for. I have a spreadsheet that i've cleaned up as the data source that has 2 tabs. The first tab is an overall count that pulls numbers from line items in the 2nd tab and includes project hours, and the second tab is a more detailed line-by-line of all incidents/injuries per project for the year.

I am at a loss as to how to create a visual that allows me to show each project as a line, with each month's frequency rate showing for the year (based on the sub-classification type), that is also filterable by company type (our 2 business units) and contractor type (subcontractors, which include one of our own sub-companies).

I want to create this setup;

but it needs to pull from this type of table for the main calculation of sub-classification type per project, per month (the "Project (Inc-Inj)" table, instead of the "Project (Stats & Lead Ind)" table which is just a total count table)

referencing the project hours from the "Project (Stats & Lead Ind)" table per project, and using our calculation of ((incident/Injury total x 200000) / project hours).

The problem with my current setup is that our company has Axiom and Bosa as General Contractors (listed in the "Contractor/GC/Project" column of the "Project (Inc-Inj)" table), but the "Project (Stats & Lead Ind)" table doesnt have that differentiation in it, which is why i'm thinking its easier if it all comes from the "Project (Inc-Inj)" table.

So essentially, we need to be able to see Axiom/Bosa frequency rates, and then be able to filter specifically for BCFI, in page 2 of the powerbi report I made.

I'm probably overcomplicating this and may need to setup the spreadsheet a bit differently to complete this with ease, but any help/insight/answers would be SO appreciated. Here's the link to the spreadsheet database source and also the powerbi report i made so its easier ...

I hope to find an answer quickly as my boss wants this report finished by tomorrow at 2pm (PST), so i'll be refreshing this entry like a friggin mad-man hoping i get a response from someone. :'(

THANK YOU!!!

[lbendlin]

If you want this to work you need to unpivot your source data to bring it into usable format.