Insights / Public safety

How to build a crime dashboard for a small police department

What should the first page show?

A first crime dashboard for a small department does not need to be a platform. It needs to answer the recurring questions from one definition of each number. Five views cover most of what a chief, a council packet and a shift commander ask for:

• Totals by NIBRS category against the prior year. NIBRS sorts offenses into crimes against persons, crimes against property and crimes against society, so those three lines are the natural top row.
• Monthly counts with last year drawn as a line. A spike reads as a spike instead of noise.
• Day of week by hour. The grid a shift commander reads first.
• Top offenses ranked on the NIBRS code on the record. No recoding into a second spreadsheet.
• Clearance, if your export carries a clearance field. If it does not, leave it off rather than estimate it.

You can see all five working on public records in our public safety demo, built on data the Olathe Police Department publishes. Olathe is a public data source for that demo, not a client.

What data do you need?

The CSV or Excel export your RMS already produces is enough to start. Calls for service come from your CAD export, if you want a workload view later. A first build does not need direct access to the records system, and on a first project it is better without it.

The one decision to make before building anything is what you count. The FBI’s NIBRS documentation says an incident can carry up to 10 offenses. So “how many crimes” can mean incidents or offenses, and the two numbers differ. Pick one for each figure, write the rule down, and put it in the model, not in each chart.

Why do NIBRS numbers differ from the old UCR numbers?

The FBI retired the Summary Reporting System (SRS) on January 1, 2021, and moved to NIBRS-only collection. SRS used the Hierarchy Rule: when one incident held several offenses, only the most serious was counted. NIBRS drops that rule and records each offense. The FBI’s own transition FAQ reports that converting 2014 NIBRS data to SRS lowered the counts by 2.1 percent. So if your dashboard trends back before your agency switched, mark the switch on the chart, or a reporting change will look like a crime change.

How do you build it in Power BI, step by step?

1. Put the exports in one folder. Save each month’s export to a SharePoint folder. Power Query’s combine files feature reads every file in a folder with the same layout into one table, so next month’s file is picked up without anyone touching the model.
2. Add a NIBRS lookup table. One row per offense code, with the offense name and its crimes-against category, taken from the FBI’s NIBRS user manual. Your export carries codes; your report wants names.
3. Add a date table and define “prior year” once. Decide what February compares to and what a partial month shows.
4. Write the measures once. Incident count, offense count, cleared count. Every page reads the same measures, which is why the numbers stop disagreeing.
5. Publish and schedule the refresh. Microsoft’s documentation allows up to 8 scheduled refreshes a day on a Power BI Pro license. There is no monthly refresh option, and a schedule pauses after two months with no one viewing the report, so someone should open it at least monthly.
6. Set row-level security if different people should see different detail. Command staff see everything, a supervisor sees a squad. Note that Microsoft’s row-level security filters only apply to people with Viewer access, not to workspace admins, members or contributors.

Can you put the dashboard on the city website?

Not the internal one. Microsoft’s own warning on Power BI’s Publish to web feature is that anyone on the internet can view the report, including detail-level data the report aggregates, even if the report does not display it. Publish to web is also unavailable for reports that rely on row-level security. If you want a public transparency page, build it from a separate model that holds only aggregated counts, with no case detail and no address-level points.

How do you check your numbers against the FBI’s?

The FBI’s Crime Data Explorer publishes agency-level NIBRS counts, so you can compare your local totals to what the FBI received. Expect small differences: state programs review submissions and timing differs. Two habits help. First, if a month shows zero in the FBI data, check whether that month was submitted before reading it as a drop. Second, if you compute a rate, compute it yourself from counts and population so you know exactly what it means.

What does it cost and how long does it take?

If you build it in house, the cost is analyst time: usually the counting rules and the lookup table take longer than the charts. If you want it built, our crime statistics dashboard is quoted from a written scope, and takes about four to six weeks from the day the export lands to the handoff session. You own the file at the end. The department view, with calls for service and shift workload, is on our public safety analytics page.

Frequently asked questions

Do we have to buy new software?

Often not. Power BI Desktop, where the model is built, is a free download. Sharing reports inside the agency needs a Power BI Pro or Premium Per User license, or a capacity, and many Microsoft 365 plans already include it. Your IT can confirm in the admin center.

Does an outside builder need access to our RMS?

No. A first dashboard can be built entirely from exports you choose. Whether any outside account is allowed inside your environment is a decision for your CJIS terminal agency coordinator, and exports avoid the question.

Can our analyst maintain it afterward?

Yes, if the counting rules live in the model and are written down. That is the main thing to insist on from whoever builds it.

Is a dashboard the same as our NIBRS submission?

No. The submission goes to your state program or the FBI. A dashboard can read the same codes and show where your local count and the submitted count part ways, which is often the most useful page an analyst gets.