Introduction

Day two of Dashboard Week started off a lot better than day one, no APIs.  Today we were provided gambling data from 1995-2022.  This data included all forms of gambling and how they performed across numerous metrics for all the states in Australia.  For example one sheet would include the annual turnover for Keno from 1995-2022 for every state.  For my dashboard I decided to look at how wagers (sports/racing bets) compared to gaming machines (pokies).  To do this I used five metrics, real turnover (adjusted for previous years), real expenditure, turnover per capita, expenditure per capita, and % of household disposable income.  Pulling this from the excel sheets was not straight forward though so today’s dashboard week started in Alteryx.

 

Alteryx

While I knew there was a more dynamic and probably even quicker way to pull the data from the spreadsheet, I initially thought I would only use four spreadsheets total so I started by manually extracting each one individually as opposed to using a macro or making my workflow dynamic.  My workflow was exactly the same for each individual spreadsheet however with small customizations where necessary for things like naming values and changing what sheet to pull from.  The workflow looked like this:

Nothing too special, just removing unnecessary rows and columns with the select, sample and data cleaning tools.  I then filtered, renamed some columns, transposed and finally joined all the sheets together to get my final data source in one sheet.  All other data preparation was done in Power BI.

 

The Dashboard

Initially I planned to do a state comparison, possibly QLD and NSW, even VIC as they are the major states and I assumed contributed the most to gambling. Once I brought the data in however, I saw that there was not a lot there so I changed it to include all the states and that is when I noticed the Northern Territory outlier.

The dashboard starts with a breakdown of expenditure and turnover by state and gambling method.  This is also visualized over time.  As you can see, NT is the third highest state by turnover although it has the lowest population in Australia.  When it is selected we can also see that almost 99% of their turnover comes from wagers and their turnover has been increasing massively since 2012 reaching an extremely high peak at the beginning of COVID.  This combined with a very low expenditure means a lot of money is being spent however the average customer is not losing at the same rate.

 

Next I looked into wager losses as the focus is on NT I wanted to zoom into their biggest contributor, wages.  I started by plotting the losses by state but noticed that the NT was so much higher than the rest of the states so I grouped the rest of the country into one line and compared it to the NT.  I created a line chart with the losses overall and per capita and in the losses per capita the NT was so much higher than the rest of the country combined it almost didn’t seem real.  I looked into why this may be and I found that The Northern Territory hosts a lot of the countries largest betting agencies as they have a lot more relaxed laws and taxes for those sort of companies.  This would make a lot of sense as to why they have such high losses, expenditure, and turnover compared to their share of the population.  At the bottom on the right I plotted turnover by loss % in a scatter plot which shows that in all states the more you spend the more chances you have of losing or at the least it doesn’t improve your chances of winning.

 

The final part of my dashboard shows the personal impact of gambling in both gaming machines and wagers.  NT spends by far the highest percent of their disposable income on gambling with over 3% of it going towards wager bets.  This was to be expected however the plot on the right shows that people in the NT have gambled the second least amount within the 12 months on both wagers and gaming machines.  This further backs up my idea that the majority of these bets are coming through the big betting companies and perhaps don’t represent the general population in the NT.

 

Conclusion

Using Power BI for this dashboard was a fun experience and I think I was able to show a lot of interesting insights in a coo, interactive way.  It is a shame that the NT has such high gambling numbers however I think I am correct in my assumption that these are just the major betting companies taking large bets as it is only in wagers and the other data I brought in showing how often residents gamble shows that they are not big gamblers compared to the rest of the country.

Mikael Nuutinen
Author: Mikael Nuutinen