As accountants, our training in the use of business graphics to communicate the message behind the figures is unparalleled. In the past, our ability to unleash this superpower has been hindered by the tools that we have readily available.
Excel graphs were OK but, compared to some of the other ways of visualising financial and other data, they were somewhat static and limited. While many accountants were producing 3D pie charts, the late Hans Rosling was playing bubble charts, animated over time, to show how the world was changing.
The latest developments in Excel, and the Power BI Excel spin-off, now give us the opportunity to create dramatic graphics that can promote fuller and deeper understanding, and thus contribute to effective decision making.
The story so far…
The Cutting Big Data Down to Size series included an introduction to Power BI. In addition, Hugh Johnson of Suntico has contributed two excellent posts on working with dates and time periods when using Power BI to visualise Sage 50 data.
In this post, we are moving on from the mechanics of using Power BI to acquire and manipulate the data, to looking at how to prepare and share a visualisation of that data. We will use our World Athletics data from the 'Big Data' series as a starting point and combine it with a table of population by country for 2017.
Assembling the data
As always, it's important to check the data carefully. In the case of the population table that we have chosen to use, a handful of countries have been entered differently in the population table compared to our World Athletics medal table table. In addition, when merging tables in Power Query or Power BI, the match is case sensitive. Our World Athletics countries have been entered in upper case and our population table in sentence case. A straightforward merge, based on country name, will result in no matches:
We can use the Transform Ribbon tab, Format command to make the Country columns in both tables uppercase:
By choosing the Right Outer join type, and filtering the Country column to show just those rows set to 'null', indicating the lack of a match, we can see the list of countries that have been entered differently:
Because we have downloaded the population figures as a 'static' CSV file we can just overwrite the unmatched countries in the underlying file.
Also, because of the way we are going to use the data, we will reverse the operation detailed in part 4 of the Big Data series that Unpivoted our Gold, Silver and Bronze columns so that they are Pivoted once more to be shown as separate columns:
We now have our data in a suitable format to visualise the relationship between World Athletic medals and current country populations, so we can choose File, Close and Apply to make it available for Power BI visualisations. Perhaps because of the Hans Rosling example, the Scatter Chart graph includes a Play axis so that it can be played over a time and date field. We can drag our data fields to the appropriate sections of our visualisation.
The Country field will be used as the legend, with the 2017 Population as the X axis – note that we will need to click on the dropdown to set this as the Average, so that we don't multiply the population by the number of medals. The Total number of medals won is used for the X axis. We can also use the size of each bubble to show another aspect of the data and, in our case, we have used it to show the number of Gold medals won.
Finally, as the only date/time field in our data set, we drag Year to be the Play Axis. This gives us a bubble chart that we can play over time to see how the number and type of medals won relates to the current population of each country and how that relationship changes over time.
The resulting graphic would be more meaningful if we used the actual populations for each year in which the World Athletics championships took place, but for the purpose of this article we have opted for simplicity:
We can click on the Play button to animate our chart over time, or drag the slider, currently at the right-hand end of the timeline, to a particular point in time.
We have used the format options for our chart to tailor it to our requirements. These options are accessed via the 'paint-roller' icon at the top of the visualisation pane. This shows how we have changed the X axis from linear to log to accommodate the wide range of different population sizes:
Next, the world
Having created our Power BI visualisation, we might want to broadcast our message to the world. We can use File, Publish, Publish to Power BI as an intermediate stage and, once our visualisation is displayed via Power BI in a browser, we can use File, Publish to Web to make it public. After some warning messages, Power BI will display a link and html code for embedding in a web page:
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.