Rare crimes spike finder (powerBI)

June 18, 2024

This short post is inspired by the Crime De-Coder blog article ‘A simple rule to identify if rare crimes have spiked’.

I thought I would try and do something similar in Microsoft PowerBI as I’ve seen PBI being used for public-facing reports by numerous law enforcement agencies in the UK and elsewhere.

1. Connect to your data. In this example, I directly connect to London homicide data from the Greater London Authority Data Store. You can add this to PowerBI using ‘Web’ to get data, and provide it the URL.

2. Create a simple count table. In this example, we are using just homicide, but it could include a category column if you wanted to create distributions for a selection of crime types on the fly. Below is how the simple count table looks in PowerBI.

1. Create a ‘PoissonRange’ table — this will be the x-axis of your visual. You can do this on the modelling tab in PBI by selecting the ‘New table’ icon. Generate a table using DAX

PoissonRange = GENERATESERIES(0, 30, 1)

1. Generate the mean value of the data series. In the example below, I use the calendar year 2022 count.

MonthlyMean = AVERAGE(Monthly_Counts[2022])

1. Create a measure that will calculate the PMF from your data

PoissonPMF = 
VAR mean = [MonthlyMean]
VAR x = MAX(PoissonRange[Value])
RETURN
IF(
    x < 0,
    BLANK(),
    EXP(-mean) * POWER(mean, x) / FACT(x)
)

1. Create a measure for the actual distribution. In this example, I use data for the 2023 calendar year.

ActualDistribution = 
CALCULATE(
    COUNTROWS(Monthly_Counts),
    FILTER(Monthly_Counts, Monthly_Counts[2023] = MAX(PoissonRange[Value]))
)

1. Display using the ‘Line and Stacked Column Chart’ visual.

I originally posted this on Medium