This project is ideal for: Power BI beginners and advanced users Data analysts Business intelligence professionals Dashboard designers Students building portfolio projects
Case study overview: where, when and why crashes happen in New York City
This Power BI dashboard turns New York City’s motor vehicle collision records into an interactive road safety analysis, showing where crashes cluster, when they peak, and what contributes to them.
The business problem. Collision data is public, but it is a very large table with one row per crash. Safety advocates, journalists, city planners and insurers cannot see patterns in thousands of rows, such as which boroughs have the most injuries, which hours are most dangerous, or how pedestrian and cyclist crashes differ from vehicle-only crashes.
The solution. A cleaned data model with Date and Time tables, a set of DAX measures for crashes, injuries and fatalities, and report pages that move from the city-wide trend to a single borough or street.
Who it is for. Transportation and road safety analysts, journalists, insurance and risk teams, and Power BI learners who want a real open-data project for a portfolio.
The data: NYC motor vehicle collisions
The source is the motor vehicle collisions dataset published through NYC Open Data, which records police-reported crashes. Each row is one crash. A typical layout includes crash date and time, borough, ZIP code, latitude and longitude, street names, counts of persons injured and killed (split into pedestrians, cyclists and motorists), contributing factors, vehicle types and a collision ID. [Adjust this list to the columns in your file and state the date range you used.]
Data quality issues to handle
- Many rows have a missing borough, ZIP code or coordinates, so geographic visuals need a rule for unknown locations.
- Contributing factors use inconsistent labels and a large Unspecified group.
- Vehicle type codes mix spellings and abbreviations for the same vehicle.
- Date and time arrive as separate text columns that must be converted to proper types.
- Counts of injured and killed are totals plus category columns, so they must be checked for consistency before summing.
Cleaning and modelling in Power Query
- Set data types. Convert crash date to Date, crash time to Time, and injury and fatality columns to whole numbers.
- Standardize text. Trim and capitalize borough, vehicle type and contributing factor values, and merge near-duplicate labels.
- Handle missing values. Replace blank boroughs with Unknown rather than dropping rows, so totals still reconcile to the source.
- Add helper columns. Create hour of day, a time bucket (Overnight, Morning, Afternoon, Evening) and a flag for crashes involving a pedestrian or cyclist.
- Build Date and Time tables. Keep dates and times in separate tables and relate both to the crash table, so you can analyse by year, month, weekday and hour.
- Model. Use the crash table as the fact table, with Date, Time, Borough and Contributing Factor as related tables where it helps filtering.
A star-style model keeps the report fast, even with a large number of crash rows, and makes every slicer behave predictably.
Key DAX measures
These measures drive the KPI cards, trends and comparisons. Table and column names are examples, so match them to your model.
Total Crashes = COUNTROWS ( Crashes )
Persons Injured = SUM ( Crashes[NumberOfPersonsInjured] )
Persons Killed = SUM ( Crashes[NumberOfPersonsKilled] )
Injuries per 100 Crashes =
DIVIDE ( [Persons Injured], [Total Crashes] ) * 100
Pedestrian Injuries = SUM ( Crashes[PedestriansInjured] )
Cyclist Injuries = SUM ( Crashes[CyclistsInjured] )
Crashes Prior Year =
CALCULATE ( [Total Crashes], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Crashes YoY % =
DIVIDE ( [Total Crashes] - [Crashes Prior Year], [Crashes Prior Year] )
Share of Crashes % =
DIVIDE ( [Total Crashes], CALCULATE ( [Total Crashes], ALL ( Crashes[Borough] ) ) )The last measure shows each borough’s share of all crashes. ALL removes the borough filter from the denominator, while other slicers still apply. Raw crash counts favour large boroughs, so consider adding a rate per population or per vehicle miles if you have that data.
Dashboard walkthrough
Each page answers one question.
| Page | Question answered | Main visuals |
|---|---|---|
| Overview | How many crashes and injuries, and how is it changing? | KPI cards, monthly trend, year-over-year change |
| Where | Which boroughs and areas are worst? | Map of crash locations, borough ranking, top streets |
| When | When do crashes peak? | Weekday by hour heat map, time bucket bars |
| Why | What contributes to crashes? | Top contributing factors, vehicle types involved |
| Vulnerable road users | How are pedestrians and cyclists affected? | Injuries by road user type, trend over time |
Design choices that help readers
- A map and a ranked bar chart sit together, so geography and totals can be read at once.
- Contributing factors exclude Unspecified in the chart, with a note showing how many crashes that hides.
- Slicers for year, borough and road user type are synced across pages.
- Drill-through from a borough opens its detail page.
Insights, business value and lessons learned
What the dashboard reveals
- [Geography: name the borough with the most crashes and the one with the most injuries, with numbers.]
- [Timing: name the weekday and time bucket with the most crashes.]
- [Causes: name the top two contributing factors, and the share of crashes marked Unspecified.]
- [Vulnerable users: give pedestrian and cyclist injuries and how they changed over the period.]
- [Trend: describe the change in crashes year over year for your selected range.]
Replace each prompt with the figure shown in your embedded dashboard.
Business value
- Safety teams can focus enforcement and street design on the places and hours that matter most.
- Journalists and advocates get a reproducible, sourced view of crash patterns.
- Insurers and risk analysts can compare boroughs, times and vehicle types in one place.
Lessons for Power BI builders
- Keep rows with missing locations and label them Unknown, so totals still match the source.
- Show rates alongside counts whenever you compare areas of different size.
- Report how much data is Unspecified, since it limits what the causes chart can say.
- Remember that police-reported data reflects reporting practices, and it shows association rather than cause.
Frequently asked questions
Where does the NYC car crash data come from? It comes from NYC Open Data’s motor vehicle collisions dataset, which records police-reported crashes in the city.
Can I build a crash analysis dashboard for free in Power BI? Yes. The data is public, and Power BI Desktop is free to build and test reports. Sharing through the Power BI service depends on your licence.
How do I show crash locations on a map in Power BI? Use the latitude and longitude columns with a map visual, and decide how to treat crashes that have no coordinates.
How do I compare boroughs fairly? Crash counts favour larger boroughs, so add a rate, such as crashes per resident, alongside totals.
What does Unspecified mean in contributing factors? It means the reporting officer did not record a specific factor. Show its share on the dashboard so readers understand the limits of the causes view.
Try it yourself
Watch the full tutorial, then rebuild the dashboard with the NYC collision data. Need a custom analytics dashboard for your organization? Contact datascientist.ca