Why Power BI?

I’ve been developing Power BI reports since 2015 and I can say that the tool has come a long way since I first began using it. Every month, Microsoft releases a series of updates that come from either users voting on features they’d like to see or from Microsoft’s own list of improvements that set Power BI apart from its competitors. One of the more recent updates allows Power Apps integration into Power BI reports. This opens the door to a new level of interaction with visualizations in the form of a mobile or web application. For example, I was speaking with a client who wanted an interface built into their Power BI report so that they could go in and change data points on the front end without having to go through the database. With the Power Apps capability, this is now a possibility for the client.

Power BI offers users with little to no understanding of data visualizations to easily build charts and graphs that transform their data into tangible business insights. The interface has a drag and drop system that doesn’t require the user to have anything more than familiarity with the data and an idea of what they want to know. And with the extensive features that go beyond just making charts, Power BI allows for more experienced users to build out complex solutions with the ability for on-screen interaction and manipulation.

When it comes down to it, I like Power BI so much that I developed my own training material that goes through the creation of a fully functioning report while making use of both simple and complex features within the tool.

Innovation in Visualizing Data

An interesting use case I’d like to share with you involves my hometown of Atlanta, which is known for struggling with a shortage of police officers in the city. Using some datasets I gathered from Kaggle.com, I wanted to create a report that would identify where in the city the highest rates of crime are, and then develop a hypothetical analysis to determine how to reduce crime in those areas given a theoretical budget.

Based on Atlanta’s 2017 crime statistics, I put together a two-tabbed report in Power BI. The first tab provides the user with a view of Atlanta and correlates higher crime rates to darker reds. The user can select specific dates to get an idea of when and where certain types of crime occurred and obtain a short description of the crime. The second tab provides a deeper analysis that allows users to see which neighborhoods have the highest crime rates, and which types of crime occur there most often. On the left panel, I added an imaginary budget and a crime rate calculation. Users can use a slider to deploy officers to their chosen neighborhood and see how the budget and crime rate could be affected.

Putting Pieces Together

When developing this report, I came up with a few questions to lead the design:

· What parts of Atlanta have the highest rates of crime?

· What types of crime occur the most and when do they occur?

· What budget does the city of Atlanta have to work with and how will deploying more officers effect that budget and crime rate?

Which parts of Atlanta have the highest rates of crime?

I wanted to create a heatmap of the city to answer this question. Unfortunately, there isn’t a default for Atlanta neighborhoods, but luckily it wasn’t too hard to create one. To create the necessary map, you need a topojson file of your boundaries.

1. I googled ‘Atlanta neighborhoods map’ and found this. I downloaded the KML file so that I could convert it to a topojson.

2. I then used a KML to geojson converter I found online to get the geojson file. I copied the text and saved it locally with the .json file path.

3. Finally, I converted the geojson to a topojson file and uploaded it to the Custom section for a shape map in Power BI. I used neighborhood and frequency from the data to fill the locations with color based on crime frequency.

With the completed heatmap, we can now see that the darker red areas have higher crime rates.

What types of crime occur the most and when do they occur?

To answer this question, I created a vertical column chart displaying types of crime on one axis and the number of times they occurred on the other. The data doesn’t have a calculation for the frequency of crimes, so I had to create my own measure to find that number.

1. I clicked the ellipses next to the table name to write my calculation.

2. A bar appears at the top of the screen to write the calculation and used a DAX formula to count each time. When sliced on a bar chart with any dimension (month, crime type, time of day, etc.), the frequency will change.

Frequency = COUNT(‘Crime Data’[Crime])

With the measure added to the data fields, I was able to create a bar chart showing frequency of crime by type and hour, as well as a line chart that shows frequency by month. I also added a slicer to the top of the page that allows the user to slice the report by month. Another piece I added was a table at the bottom of the report, which allows users to look at a line-by-line view of the crimes.

What budget does the city of Atlanta have to work with and how will deploying more officers effect that budget and crime rate?

To complete this analysis, I created a second tab in order to do a deeper dive into the crime. I first created an imaginary budget of $10,000. From there, I created a “what if” parameter to allow the users to see a change when officers are deployed to different neighborhoods. Finally, I calculated two crime rates, one that remains static, and one that changes based on the number of deployed officers.

1. From the Modeling tab, I selected the New Parameter option.

2. I then filled in the details for the parameter. I wanted to have a slicer that increments by one and maxes out at ten.

3. I had to create a new table in the fields section that shows Officers Deployed (1 to 10 value) and the Officers Deployed Value, which changes based on the slicer on the page.

4. After I had the parameter set up, I created a measure for crime rate. This measure calculates the crime rate by dividing the frequency of a specific crime (sliced by month, time, crime type, etc.) by the total overall crimes, and then subtracts one-tenth of the value from Officers Deployed. The subtraction portion of the calculation is arbitrarily assigned, but in an actual analysis, we could use a properly calculated change.

Crime Rate (Analysis) = ([Frequency]/SUMX( ALL(‘Crime Data’), [Frequency]))- (‘Officers Deployed’[Officers Deployed Value]*0.01)

5. Now users can change the crime rate analysis value with the slicer that was created before. With the newly calculated crime rate, I also created a couple visuals to show which neighborhoods have the highest crime rates, as well as a line chart that shows when the highest influxes of crime occur.

Final Thoughts

There are many tools out there to visualize data, but Power BI is one of the front-runners for allowing users to get in touch with their creative side while giving them the ability to develop something complex and meaningful. It provides users with a simple interface that can transform into a powerful, front-facing engine with use cases in predictive analytics, self-service and analytical innovation. And because of the continuous improvements and ideas that are shared by Microsoft and the Power BI community, I’ve had the opportunity to try new things that I wouldn’t have originally attempted with the tool.

As you can see, data can be a powerful tool not only for business, but for driving meaningful change in the world by bringing a new perspective on old problems. This city of Atlanta crime report provides a visual interface for the departments that oversee budgets and crime statistics in Atlanta. With the ability to predict costs and future needs, they will be able to justify deploying more officers to high crime areas and visualize the changes in crime rate post deployment.