Dashboard Week day 4 – The more you know, the happier you are?

by | May 17, 2023 | Vincent Yim

On to day 4 of Dashboard Week!

We had an inkling that one day this week we’d have to create a dashboard in Power BI instead of our usual tool of choice Tableau. This was the day. Unlike the rest of my cohort, I haven’t actually had the chance to work much with this offering from Microsoft – I was the project lead during the client project week when we worked exclusively with Power BI. However, before I started at The Data School I’ve had a lot of experience with Power Query at my previous job, and I’ve also learnt a bit of DAX on my own, so I was keen to get my hands dirty and finally make something in Power BI, for Dashboard Week no less.

See other posts in my Dashboard Week series:

Data exploration

The topic Coach David and Coach Ross gave us was to do with happiness – what factors correlate with the World Happiness Index for different countries? Is it wealth or health? Or something else entirely? David also pointed out it would also be useful to find what factors didn’t correlate with the Happiness Index and include those in our dashboards.

While we weren’t limited to using Power Query for data preparation, I set myself the challenge of only using it (and avoiding Alteryx altogether) for this one day. We were pointed to two sources of data – OECD Data and World Bank Open Data. We were given a few datasets to start us off with but were otherwise encouraged to find supplementary data wherever we could find it. One of the datasets provided was from the World Bank and had the GDP for different countries from 1961 to 2021. I decided to make use of this, and that naturally led me to look for more data from the World Bank, as I knew the format would be more or less consistent and it would thus save me some time on data cleaning.

Power Query workflow

Deciding on the focus of my viz

In the end, I found and cleaned three more datasets that also contained data for various countries from 1961 onwards:

  • Adult literacy rate
  • Tertiary education enrolment
  • Government expenditure on education as % of GDP

As you can tell, the angle I picked for my dashboard is on whether the level of education and literacy in a country has any effect on its World Happiness Index throughout the years. As I had suspected, the format for the supplementary datasets were similar, so I had managed to merge them all and clean the resulting data in Power Query without too much trouble. The datasets also came with metadata on the countries, splitting them up into different regions in the world (e.g. Europe & Central Asia, North America), so I included this as well as an extra level of aggregation for the analysis later.

Power BI viz

I am quite proud of my dashboard in the end. Admittedly, it took a lot of Googling to find out what Power BI can and can’t do (compared with Tableau, which I’m much more familiar with). However, I’m very glad I took the time I did, especially in figuring out the DAX for the BANs (big-ass numbers) in the middle panel, which dynamically update (along with the pane title and charts) depending on the country or countries selected and year chosen.

What this dashboard shows is that:

  • Positive correlations with a given country’s World Happiness Index are more obvious in adult literacy rate and tertiary education enrolment.
  • There is much less correlation with happiness in terms of a country’s annual GDP growth and government expenditure on education.
  • Perhaps unsurprisingly, countries in Europe & Central Asia tend to be happier, whereas those in Sub-Saharan Africa are more likely to be on the other end of the spectrum.

I haven’t figured out how to publish my final Power BI report with all of the interactivity retained, so here’s a screenshot in the meantime:

Power BI report

BANs and panel title dynamically update depending on country and year selected

BANs and panel title dynamically update depending on country and year selected

Vincent Ging Ho Yim
Author: Vincent Ging Ho Yim

Vincent has always enjoyed learning new things as well as finding elegant and efficient solutions to problems since childhood. He studied linguistics at university and has subsequently worked in theatre lighting and broadcast captioning. In his previous job he found his passion working with data and decided to pursue a change in career. In his spare time he likes reading, learning languages (both human and programming ones) and playing Pic-a-Pix and sudoku. He loves laksa, sushi and burritos.