Summary

Commodore Builders recently began transitioning to a data-driven project management approach. I have taken the initiative to learn Power BI and assist our Director of Internal Operations with creating dashboards and KPIs for the company’s senior leaders. These visualizations will help our leadership and management teams measure progress on project-level safety/quality reviews, client-requested COVID compliance tracking, and organizational KPIs.

Some organizational benefits to the work I’ve already completed include:

• Saving internal teams 1-2 weeks of effort each month by automating OSHA lost time metrics
• Saving project teams 30-60 minutes each day by aggregating vaccination data into one succinct table
• Saving internal teams 2-5 days of effort each month by automating KPI metric calculation/visualization
• Saving QA/QC and Safety management leaders at least 2 days of effort by compiling up-to-date compliance measures and identifying specific areas/projects to improve

Projects

Individual Checklist Data Aggregation

As part of our QA/QC and safety programs, employees use Procore software to fill out inspections (checklists). Each inspection includes questions to track project progress, subcontractor onboarding, safety/quality standards, etc..

Commodore wanted to extract information from these inspections to set and track measures that would lead to higher project profitability and superior safety performance.

A key way to enable goal creation and tracking was to view the data from each inspection in one row. An obstacle to doing so was the Procore inspection table data structure. In the table each individual question was listed as its own record. For example, if an inspection had four questions, the corresponding data table would look similar to the below example, which includes two inspections:

With the above structure, it would not be possible to view or analyze the detail of an inspection efficiently. I was tasked with determining whether or not each inspection could be collapsed into one row for viewing and aggregation purposes.

When investigating Power BI’s native capabilities, I could not find a function or built-in method to perform this type of aggregation/filtering.

Upon further investigation, I found a solution via the Power BI community forums here.

After reading this solution, I sorted through the code and extracted the portion I needed. To solve the problem, I:

• Duplicated the original table
• Filtered columns out of the duplicate table
• Pivoted the table by the Question column, with the response column as the value field

This yielded a table similar to the below:

With the above data structure in place, I utilized the advanced groupby syntax to collapse the data into one row. For the sample data above, the query would look like the below and yield the following table:

New Table = Table.Group(#"Previous_Table", ("ChecklistID"), 
    (("First_Name", each List.First(List.RemoveNulls([First_Name]))),
    ("Last_Name", each List.First(List.RemoveNulls([Last_Name]))),
    ("Role", each List.First(List.RemoveNulls([Role]))),
    ("Company", each List.First(List.RemoveNulls([Company]))))

Now the team could view checklist records efficiently and create measures/KPIs based on responses.

Below are a few examples of how this checklist data aggregation process has been used.

Vaccination Dashboard

Several of Commodore’s clients required that all workers on site be vaccinated for COVID. To confirm vaccination status, workers on site filled out an inspection verifying their vaccination status.

I used the vaccination questionnaire data to create a searchable table so team members could determine whether a given worker was vaccinated. This led to a 50% reduction in time to check-in and verify status of workers, leading to a more efficient start to the work day for everyone.

Subcontractor Kickoff Tracking

Before a new subcontractor begins work, Commodore requires them to have a kickoff meeting with the project team and fill out a corresponding inspection summary of the meeting. The teams discuss many items including financial logistics, safety plans, scope of work and start date.

It is critical that these meetings take place at least 2 weeks prior to the subcontractor start date, so everyone has the same expectations for the work to be completed before starting. To this end, I created a dashboard to track:

• The status of all kickoff meetings, including all planned meetings
• Measures showing how many total meetings were done on time, late, or were pending completion
• A % score showing how many meetings have been complete on time

With the above dashboard, our QA/QC team can now track compliance on QA/QC program requirements and inquire more specifically about work to be completed.

Subcontractor Evaluation Dashboard

The Commodore pre-construction team is incorporating data into procurement processes. Before analytics data was available, it was difficult and time-consuming to evaluate subcontractor performance objectively. With data available, the team now wants to compare contractor safety and performance data apples-to-apples.

I was tasked with created several safety visuals, and saved the procurement team several days per estimate by enabling performance comparison within seconds rather than guessing/sorting through multiple datasets for days. Going forward, this will help the team make quicker, more informed award decisions based on real-time data and lead to safer job sites.

To enable the above pre-construction goals, I created a subcontractor performance dashboard so that key on site measures could be viewed and bidder scores compared.

This dashboard had several visuals, including:

• Horizontal bar plot showing the number of toolbox talks done per week. A contractor must perform one per project per week to remind team members about best safety practices.
• Horizontal bar plot showing how quickly a contractor responds to observations. Observations often represent issues, so timely responses and closure of issues can be critical to project QA/QC performance
• Horizontal bar plot categorizing the types of observations assigned to a subcontractor. The team would note how many positive and negative observations a contractor has as an indication of whether work is performed safely and with minimal defect/re-work rate.
• Vertical bar plot showing the number of SPAs done per conractor. SPAs are a summary of a trade’s planned work for the day. This document must be filled out daily and is used as a record in case any safety issues arise.
• Horizontal bar plot showing PPE score. Trades must abide by Commodore’s PPE policy, and the Commodore team rates contractors weekly on their compliance. Higher scores indicate better compliance to standards, which leads to a safer work place and fewer injuries.

With the above dashboard created, our pre-construction team can now review contractor safety and performance data and incorporate that as part of the award process.

KPIs

Commodore’s senior leadership team of ops directors, board members, and C-suite executives recently came up with a list of KPIs to be monitored via Power BI. I worked with our internal operations director to find relevant datasets, compute KPIs, and put them on a dashboards/visuals. These KPIs will help lead to higher profits and a targetted approach to company growth; some key measures I built include the below.

Project Fee Tracking

Commodore has a target fee goal for each project, which gets aggregated into an overall company target fee percentage. Creating accurate fee projections is critical to measuring the health and potential of the company. To monitor overall fee, I built a chart that will allow leaders to track how well the company is performing against overall fee goals and drill down into projects that are bringing in less fee than anticipated.

To do this I created two measures:

The first measure was the target fee percentage for a project, which is input in a table as a dollar value.

The second measure was the current project fee projection. This figure is updated any time a new change order or other contract alteration is entered into our databases. I determined all the relevant entry fields and filters and aggregated them into one measure that could be broken down by project.

I plotted these measures on one line plot over the life of a project. This will allow company leaders to compare target and actual fee, analyze trends, and determine how to adjust to meet company revenue goals.

Projects Ahead on Schedule (and %; by finish variance on status)

Construction projects need to finish before a contractual date, or else the general contractor risks absorbing overhead cost and/or liquidated damages levied by the client. As such, company leaders wanted to see a dashboard showing how projects are progressing on their respective target schedules, and an overall percent of projects that are on schedule.

To create this schedule KPI, I used two tables:

• The first was an overall project table, which held basic information about all projects in the company portfolio.
• The second table contained schedule review data for projects, contained within an inspection table.
• I left joined these two tables, so that the team could see which projects had inspections conducted.
• Using this merged table, I calculated the total number of projects ahead of schedule and converted that to a percentage

With this information, leaders can easily see determine which projects are doing well, and which may need additional intervention or funds to get back on schedule.

Total Schedule Mitigation (change in finish variance after schedule update)

Another important schedule-based KPI is schedule mitigation. Schedule mitigation refers to schedule days saved (or lost) by actualizing and optimizing activity sequencing during a schedule review. Using inputs from individual inspections, I aggregated the total schedule mitigation figures into one measure so executives could track how project schedule variances are shifting over time.

Project Kickoff meetings held on time

Project kickoffs are an important part of the project pre-construction process. Each project team must participate in kickoffs to address the following: Safety, QA/QC, and Risk planning.

To help ensure these meetings occur, I created KPI measures showing the percent of active projects that have held these meetings. I also created a table that would allow managers to efficiently find projects that haven’t held these meetings, allowing for quicker scheduling and higher compliance.

OSHA Incident Rate Tracking

A company’s OSHA lost time incident rate (LTIR) measures how often serious injuries occur within a company, based on incidents and total hours worked. This rate not only indicates how safe a company tends to be, but is also an important factor in determining insurance rates and job awards.

I created a measure to track the company LTIR, both presently and over any past timeframe. To do so I pulled data from incident, total internal hours, and total labor hours tables. I applied filters to these data tables to ensure that only the correct time and injury types were recorded, and then used these results to create the OSHA LTIR measure for the company. In addition, I plotted this value by month and quarter so the team could see how our incident rate is changing over time.

This KPI and graphs can now be used to set better company safety goals and better define a recovery path if the LTIR is trending too high.

Project Level Data Aggregation

Senior leadership wanted to view what percent of projects perform their mandatory inspections and kickoffs, because compliance in these areas yields better quality, higher safety scores and greater likelihood of making a profit. To meet these demands, I built two dashboards.

QA/QC, Safety, and Internal Kickoff Status

To track kickoff meetings, I merged two existing queries:

• A query showing all Commodore projects
• A checklist aggregation showing whether a team has performed each of the required kickoffs and whether the inspection was done on time

I displayed the above information in a dashboard similar to the below, allowing company leaders to quickly evaluate what projects to target for kickoff meetings in the near future.

Weekly Inspection Status Tracker

Each project is required to have a weekly safety manager inspection, superintendent inspection, and PM inspection regarding safety and quality on the jobsite.

To track whether inspection frequency goals were being met, I created a table to track the number of weekly inspections of each type were conducted by a project team and a corresponding percentage score showing what percent of inspections were completed weekly.

Weekly Inspection Table

I also rolled the above scores and values up into an overall project compliance score, which could be filtered for any period of time

Inspection Summary

With this, leaders can now address gaps in inspection protocol which will ultimately lead to safer job sites and decreased re-work cost.

Conclusion

Over the past year, I have learned Power BI to assist Commodore Builders’ build-out of data-driven reporting and progress tracking. In this time, I have been assigned tasks to help the company accomplish tasks including the following:

• Aggregate checklist data to track vaccination status, kickoff meeting status, and project schedule updates
• Create a subcontractor evaluation dashboard for our preconstruction team
• Develop organizational KPIs including fee tracking, schedule mitigation, projects ahead on schedule, and project kickoff status
• Aggregate safety and payroll data to track the company’s OSHA lost time rate
• Re-structure and combine data to track mandatory project and team inspections

By completing the above tasks, among others, I am enabling more accurate and efficient tracking in all sectors of the company. This will enable leadership to gain insights to improve profitability, safety and company size.

My work will also enable the next phase of data-driven growth in the company, where dashboards are created for project teams to help them manage, review, and analyze project-level initiatives. This will save time, possibly more than 50% for some tasks, and lead to quicker, more proactive quality and safety issue resolution.