Power BI School Safety Dashboard: Multi-School Reporting Guide
Managing health and safety data across multiple schools can be challenging. Each school often tracks its data differently, uses separate spreadsheets, and follows slightly different reporting schedules. What makes this especially complex is that school groups need to see both individual school performance and group-wide trends - a task that becomes increasingly difficult with traditional reporting methods.
Power BI offers a solution to these challenges. With it, you can transform these varied data sources into clear, actionable reports that help keep students and staff safe. By centralising your data in Power BI, you create a single source of truth that works for individual schools while giving group administrators the oversight they need. The platform's dynamic, interactive reports are particularly valuable for multi-school groups, as they consolidate various data sources into one platform and offer real-time updates. In this blog, we aim to show you how to build a comprehensive Power BI dashboard for school safety reporting. You'll learn how to set up key components including:
- Safety Compliance Reports (SCR) to track staff certifications and training
- Student incident tracking for accidents and safety events
- Safety drill monitoring including fire, lockdown and earthquake drills
- Action plan management with priority tracking
To build this dashboard effectively, we need to start with a solid foundation. First, we'll look at how to structure your Power BI model to handle data from multiple schools efficiently.
If these reporting challenges sound familiar, we'd love to help streamline your school group's safety monitoring. Give us a message using the form below for a free discovery session.
Data Model Setup
In the Power BI model, each health and safety subject is represented as a separate fact table, which connects to a common calendar table and a location table, as well as its own dimension tables. Fact tables should be inner joined to their dimension tables to keep only valid records. The location table is linked to the region table and the school group (parent school), which in turn connects to each individual sub-school (child school).
To enhance clarity, the model can be divided by fact tables, making their relationships easier to understand in the 'All Tables' view. For Row-Level Security (RLS), regional users apply their specific region values, while school users must use both their user email (USERPRINCIPALNAME) and a region filter to restrict access to their corresponding region
Creation of calendar table
A well-structured calendar table is essential for all reports, as it allows different fact tables to share a consistent timeline while accommodating varying reporting periods. Specific fields can simplify calculations, such as flags to indicate the current year and current period, as well as the previous year and previous period for different subjects (e.g., SCR, Safety, Actions).
In addition to standard calendar values like date, month, calendar year, and financial year, creating additional columns in integer format—such as date_key, month_year_order, CY_flag, and PY_flag—can enhance functionality.
In situations where a new financial year begins but the data is not yet fully available, the report should still display results from the previous financial year. To manage this, a hardcoded [CY] can be implemented to control reporting needs until the new data is ready for use.
Reporting periods can vary by subject. For example, the SCR report is generated monthly and requires access to the latest month-year period. The Safety report is a year-to-date (YTD) report that counts from the beginning of the financial year to the latest month-year. The Actions report encompasses all periods. A well-designed calendar table effectively manages these different reporting requirements.
Building the Safety Compliance Report (SCR)
The Safety Compliance Report (SCR) evaluates whether a workplace or organisation is meeting safety regulations and standards. This report typically includes metrics such as the percentage of staff compliance in various areas, including SCR Compliance, References, Criminal and Background Checks, Safeguarding Training, and Safer Recruitment Training. Additionally, it can show the percentage of schools within a multi-school group that fall outside acceptable tolerance levels as defined by the organisation.
As a monthly report, the SCR reflects performance for the most recent month. Staff numbers can be standardised by using the SCR area staff metric, as the number of staff may vary across different compliance areas. Calculating the percentage of compliant staff in each area for the latest month is straightforward. e.g.
% Compliant Staff - SCR = CALCULATE ([% Compliant Staff], 'scr'[area] = "SCR")
To enhance table readability, the font color for compliant percentages can be formatted based on the school’s standards, and icons can be added to illustrate changes compared to the previous month. For effective comparisons, using an integer year-month format (e.g. “yyyymm”) is recommended to clearly indicate the latest and previous periods. If the previous period lacks data for month-to-month comparison, it should be displayed as blank, without any icons.
Need help turning your safety data into actionable insights? Our team has decades of experience building these dashboards for schools and MATs across the country. Let's talk about your reporting needs.
In some cases, compliance standards do not need to be set at 100%. For instance, a school might define compliance as a staff compliance rate greater than 98%. In such instances, a filter can be applied to identify non-compliant cases accordingly.
FILTER ('scr', DIVIDE('scr'[compliant], 'scr'[no_staff]) < 0.98)
There are typically two types of data errors that can occur in staff counts. The first type arises when the number of staff in each assessment area differs from the numbers recorded in the SCR area. The second type occurs when the sum of compliant and non-compliant staff does not equal the total staff count. Power BI can be instrumental in identifying these errors for further investigation.
For the first type of error, SCR area staff counts serve as the standard metric. Consequently, areas with differing staff numbers from the SCR area can be flagged for further examination. Power BI's Power Query can be utilised to group data by date, location, area, and staff numbers, allowing for a pivot table that summarises the total number of staff in each area by date and location. This pivoting process makes it straightforward to filter out discrepancies where staff counts do not align with the SCR area staff numbers.
For the second type of data error, identifying discrepancies is straightforward in Power Query. You simply need to add a flag to highlight the discrepancies and then apply a filter to isolate them.
if ([compliant] + [non_compliant]) <>[no_staff] then "Compliant + Non Compliant <> Staff Count" else 0
Creating Safety Reports
Safety reports can encompass student accidents (classified as major, serious, or minor) and various drills (such as fire drills, earthquake drills, lockdown drills, etc.). To generate a year-to-date (YTD) report, the report page filter can utilise the current financial year flag created in the calendar table. For example, a flag set to 1 (indicating the financial year is [CY]) can be hardcoded to control the selected financial year.
It’s important to note that not all schools or areas conduct earthquake drills, so these should be displayed as blank when not applicable. Information about drills is typically stored in the school table. The number of drills can be conditionally formatted in tables based on the school’s standards for each drill requirement, allowing for a clear presentation of their year-to-date performance.
The rate of student accidents, particularly major or serious incidents per 100 students, is a crucial KPI for schools. Comparing the current year-to-date (YTD) KPI with the same period from the previous year helps school managers assess changes in student safety, because the financial year consistently begins on the same date each year (e.g., August 1st).
One challenge is ensuring that the end month of the previous financial year aligns with the current financial month for accurate comparison. This can be calculated as illustrated in the example below (the ‘All’ function is to make the current financial year page filter has no impact on previous financial year):
YTD_Major_S_Accidents Total PY =
VAR CurrentDate = MAX('incident'[incident_date])
VAR EndOfPreviousYearDate = IF(NOT(ISBLANK(CurrentDate)), DATE(YEAR(CurrentDate) - 1, MONTH(CurrentDate), DAY(CurrentDate)), BLANK())
VAR StartOfPreviousYear = IF(NOT(ISBLANK([CY - Safety])), DATE([CY - Safety] - 2, 8, 1), BLANK())
RETURN
IF( NOT(ISBLANK(EndOfPreviousYearDate)) && NOT(ISBLANK(StartOfPreviousYear)),
CALCULATE( [Major_S_Accidents Total],
DATESBETWEEN(HSReportingCalendar[Date], StartOfPreviousYear, EndOfPreviousYearDate),
ALL(HSReportingCalendar[Safety Flag CY]) ), BLANK())
When incident location information is available, the safety report also can pinpoint those top locations based on high incident rate such as sports fields, classroom, playground etc.
Actions Report with Risk-Level Monitoring
The Actions Report includes both open and closed actions, categorised by priority (risk levels): high, medium, and low, for both action type safety and safeguarding. A map can be utilised to display the total number of open and closed actions for each region, with the ability to drill down to school level for more detailed information. Example of calculating the closed safeguarding high actions as below:
Closed Safeguarding High Actions = CALCULATE( COUNTROWS('action') ,
FILTER(dim_action_source,dim_action_source[Action - Clean] = "Safeguarding"),
FILTER(dim_risk_matrix, dim_risk_matrix[risk_matrix_value] = "High"),
FILTER( 'action','action'[sn_status] = "Closed"))
The trend of open and closed actions over the last 12 months can be displayed continuously. This is easily achieved by using the top 12 year_month_key values from the calendar table, ensuring that the latest 12 months’ results are always shown.
Effective risk monitoring can be the difference between proactive safety management and reactive problem-solving. Want to learn how these dashboards can be built with your unique school data? Book a discovery call with our specialists today.
Remember…
Building a multi-school safety dashboard in Power BI might seem complex at first, but the benefits far outweigh the initial setup effort. With a properly structured data model, flexible calendar table, and well-designed reports, you can transform scattered spreadsheets into a powerful monitoring tool.
The key to success lies in the foundation: a robust data model that connects fact tables to shared calendar and location tables, strong security controls that protect sensitive information, and thoughtful report design that makes data accessible to all stakeholders.
By implementing this system, you'll gain:
Real-time visibility into safety compliance across all schools
Early warning of potential safety issues through trend monitoring
Consistent reporting that saves time and reduces errors
Clear accountability with role-based security
Evidence-based insights for better decision making
Most importantly, you'll have a scalable system that grows with your school group while keeping student and staff safety at the forefront.