Save content
Have you found this content useful? Use the button above to save it to your profile.

Using Power BI to save the world

21st Nov 2017
Save content
Have you found this content useful? Use the button above to save it to your profile.

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:


This should be our example Power BI visualisation.


Replies (2)

Please login or register to join the discussion.

Hugh Johnson
By Hugh Johnson
23rd Nov 2017 08:37

Simon, I think that your article indirectly touches on a number of reasons why I think that Microsoft Power BI can become the de facto analysis tool for accountants.

Last year I was lucky enough to have on my team a guy with many years BI experience across many tools. He was new to Power BI though and the first thing I asked him to do was to review Power BI against his experience with other tools.

His conclusion was that pretty much everything in Power BI was functionality that he had more or less seen in other products. However, he said that he had never before seen all of this brought together into one product.

What your article highlights very well is how you can use Power BI as the single "go to" place to import data, clean it up and transform it, analyse it and then publish some stunning visualisations of your analysis.

Accountants as you say "get" business graphics, but they are very time-constrained. Being able to do all of this from within a single tool-set should not be underestimated. If you add to that the fact that Power BI is so inexpensive (the Desktop modelling product is completely free) and that the formulas that you create are very familiar to the Excel formulas you are already familiar with kind of makes it a "no-brainer".

Now consider that Sage 50c now comes with a seat of Office 365 and that every seat of Office 365 comes with a Power BI pro user licence; it is a technology that is also quickly becoming the analysis tool of choice for accountants' clients.

Thanks (1)
Replying to hughjohnson:
Simon Hurst
By Simon Hurst
23rd Nov 2017 10:46

Thanks Hugh. I have come to Power BI from a very Excel-based direction so it's very interesting to hear your views from the BI side. I enjoyed your two articles on Power BI and Sage and share your enthusiasm for Power BI as a an exciting development for accountants.

Thanks (0)