This blog is part of the Dashboard Week series. If you want to know more about what the Dashboard Week is, have a look at the first blog in the series.

 

So, Day 02. There wasn’t much of a pause between the end of our presentation of the previous dashboard before we were sent diving straight into the second one.

The Challenge

The Australian Bureau of Statistics (ABS) publishes data on crime and justice on their website for the public to access freely. There are data on various aspects of the criminal justice system, and we needed to pick one aspect, understand the data, augment it with other data as we deem necessary, and produce a Power BI report.

I decided to analyse the data on prisoners and corrective services. Honestly, the main reason is because most of the other areas are really, really grim and depressing. That is not to say that prisoners and corrective services are all sunshine and roses, but I just find it easier to stomach.

The Data

The needs to maintain anonymity of the data and to ensure it’s suitable for the wider public meant that the ABS only publishes aggregated data in a cross-tabulated format (similar to a pivot table in Excel, for example), complete with loads of headers, notes, and description in multiple Excel spreadsheets, making the data preparation an interesting challenge.

On top of that, unlike the census data, the crime and justice data in the ABS’ TableBuilder/micro data portal is not up to date (IIRC, as of 5 Mar 2024, the most recent data is for 2018), so I couldn’t just use that as a shortcut.

We were challenged to process the data in Alteryx (as opposed to, say, doing it all in Excel) and so, broadly, I did the following for each metric that I’m interested in: (1) imported the Excel sheet into Alteryx, (2) removed non-data rows, (3) restructured the data into a long format, (3) cleaned individual fields where necessary, (4) joined different metrics into one table where it made sense, but they were mostly kept in separate tables due to different levels of aggregation.

I also made use of the ABS TableBuilder to get some state-level population data, so as that I could make per capita calculations for fairer comparisons.

Visualisation

My initial design philosophy for this Power BI report is, “let’s get some sleep tonight”. However, after deciding to put a choropleth map right at the top of the dashboard (I’m just going to call it a “dashboard” for ease) and to colour it with hues of red which, in turn, led me to use the dark base map, the theme pretty much chose itself. Not wanting to make the map stand out too much, I matched the dashboard background to the map, and aligned the colours of all other elements accordingly.

The time constraints were still a challenge though. I had to make a decision to cut down on the number of charts in the dashboard and yet deliver some insights, so I went with an explanatory approach with a narrow focus: the high per capita incarceration rates in Northern Territory. I also decided not to add in bells and whistles such as buttons, bookmarks, switch measures, etc., but instead focused on delivering a coherent analysis aided by a running commentary next to each of my three charts.

And here is my final dashboard:

 

 

J Tay
Author: J Tay