Clearance Rates Analysis
For this Power BI report, I used data from the Murder Accountability Project (MAP). This report uses information about homicides in the United States. The data set from this organization highlights unsolved murders. This data set showcases how data analytics can be a force of good. For example, Thomas Hargrove states:
“We’ve found that city by city the rate of murder tends to rise when the rate at which homicides are cleared through arrests decline.” (9:17)
Hargrove also mentions an inverse relationship between solving murders and fewer deaths. In this report, I will attempt to explore any features that explain this relationship. Then I will recommend any solutions that could help increase the clearance rate.
I connected two data sources in the Power BI Desktop from murderdata.org: SHR76_17.csv and UCR65_18.csv. By using the Query Editor, I renamed the SHR76_17 table to “Clearance Rates”. In this table, I also changed the “year” column to an integer. I changed the label “MRD” column to “reported” and changed it to an integer whole number. I changed the label “CLR” column to “Cleared” as in cases solved by arrests and changed that data type to integer. I used another table “UCR65_18” that I renamed “Search Cases”. I also changed the “year” column to an integer. In the column “Solved” the data type changed to integers 1 for “yes” and 0 for “no”. This allowed the column “Solved” to have cleared cases summed up as a field.
I created a “many to many” relationship using the columns “Agency” and “State” present in both tables. In the data pane, I used the DAX expression [& “, “ &] to attach two columns together. In tying those tables together, I created a calculated column named “AgencyState”. The table “Clearance Rates” does not have a calculated clearance rate. To find this you must sum the “Cleared” cases and divide it by the sum of “Reported” cases. Because of this missing rate in the table I created a calculated measure to show the clearance rate.
I used various visualization techniques to analyze the clearance rate from different regions. I used a line chart, a filled map, a column chart, and a bar chart. The line chart “Reported and Clearance Rate by Year”, illustrates reported murders versus clearance rates. As mentioned before the more reported cases the lower the clearance rate. The higher the clearance rate the lower the reported murders. The filled map “Clearance Rate by State”, pinpoints dark areas of low clearance rate. The column chart “Bottom Ten Clearance Rate by State” marks the states with the lowest clearance rate. Finally, the bar chart “Clearance Rate by Metropolitan Statistical Area”, indicates the city areas with low clearance. Two slicers let you select the year and the state you want to focus on.
The bottom 5 states have a clearance rate of less than 60%. These states are New York, Alabama, Indiana, Michigan, and Illinois. By using the State selector, you can view “Reported and Clearance Rate by Year” based on that state. You can then find the Metropolitan areas that are doing the worst in solving murders. This dashboard shows the United States is not doing enough to solve murders. The United States has been solving fewer murders than reported since the late ’70s. More murderers are roaming free looking for the next opportunity to strike.
It is often said that you are only as strong as your weakest link. This dashboard shows these weak points. A recommendation I would make is to focus on states with low clearance rates. Identifying pain points shows which states and cities need the most help. The worst states in this report are Indiana, Michigan, and Illinois. Within those states, the metropolitan areas have much worse clearance rates. Increasing funding and training will improve their clearance rates. As their clearance rates improve there will be fewer reported cases of murder.
Contact me
Email: Sancaa94@gmail.com
Portfolio: http://asketez.com/
GitHub: https://github.com/Sanca94
Kaggle: https://www.kaggle.com/sancaa94
