Where is crime growing fastest in Denver?

In my last post I showed how to transform public (and free!) crime data from the City of Denver's Open Data Catalog into an in-memory analytical playground using Microsoft Power View on SharePoint and a SSAS tabular database.

In this post, I am going to show how to build the same Power View data visualizations, but only using Excel 2013 this time.  In addition to looking at which areas have the highest number of crimes, we will also investigate the rate of growth (or decline) in neighborhoods throughout Denver. 

First, we can see that the following types of crimes declined from 2009 to 2013:

1. theft from motor vehicles
2. white-collar crime
3. auto-theft

Next, we see an increase in the following categories:

1. "all-other-crimes"
2. drug-alcohol
3. larceny
4. public disorder

Overall, the number of crimes increased from 2009 to 2013.  2014 data is only up to mid-November so it shows a decrease from 2013 but it will probably be higher then 2013 when the data is updated in the new year.

Shown another way, the overall trend definitely shows an increase in arrests:

If we look at number of arrests per year by area, we can see the following neighborhoods have the fastest rate of growth:

1. Five-Points
2. CBD (central business district)
3. Capitol Hill
4. Montebello
5. Stapleton
6. East Colfax

It is hard to discern because the above chart is so cluttered. If you filter for just those above mentioned neighborhoods, it becomes easier to see:

Below is a video of using Power View within Excel 2013 to make these slides. 

Building this Power View in Excel is much simpler and requires less steps than when you use Power View in SharePoint. In a later post, I will talk about the differences between Power View in Excel and SharePoint. 

Step 1: Get the Data

File can be downloaded for free at the City of Denver Open Data Catalog 

Step 2: Create Power View in Excel 2013

1. Launch Excel 2013.

2. Create New Workbook.

3. Insert Power View:

4. While the Power View sheet is activated, click on PowerPivot menu and then click on the Manage icon:

5. When the new PowerPivot window appears, click on the icon that say "From Other Sources":

6. In the Table Import Wizard, select the Text File source, usually at the bottom of the wizard:

7. Click on Next, then browse to where you save the Crime CSV file on your computer.

8. Make sure that "Use first row as column headers" is checked:

9. Go into Data View in order to add a calculated column to the flat file that you just imported:

10. Create a new column in the data table and put a DAX formula such as =Year([First_Occurrence_Date]). Rename the column to "YEAR" after entering the DAX formula.

11. Close the PowerPivot window which will return you to the Power View window.

SQL Fix-It Guy
I am a Denver-based database developer, trainer and coach who has spent the past two decades helping leading organizations turn their data into competitive intelligence.  I have built and fine-tuned reporting capabilities for hospitals, energy companies, banks and government agencies and I am available for projects small and large.  

Noah Meyer (noah@sqlfixitguy.com)
Cell: 347-678-8279