Welcome to the ‘Age of Analytics’ where the best way to make business decisions is with the help of data and visualisations. Alexander Linden, research vice president at Gartner states: “Analytics technology has evolved to a point where it adapts to the needs of employees and customers. Users are no longer forced to adopt traditional approaches.” To solve these business problems, Gartner has identified two trends:

1. The rise of AI and deep learning
2. Intelligent and augmented insights

In this two-part blog, we will be focusing on the second trend of Intelligent and augmented insights. This trend focuses on relevancy as the key for analytics where the user does not need to search for information, instead this is delivered to them pre-emptively. Gartner (2018) predicts that by 2021, “…75% of prebuilt reports will be replaced with automated insights.”

Aim

The aim of this blog is to get you started on creating your first BI dashboard using simple ABS data in Power BI. Power BI can provide quick insights and is a basic form of augmented data discovery. I am not necessarily advocating you run out to buy the software—the reason I’ve chosen it as a starter is that most businesses have Microsoft Office and will already have access to the software. In addition, each year Gartner publishes a magic quadrant and for 2019 Power BI is ranked clearly ahead of the other Business Intelligence (BI) platforms.

We are going create the following dashboard:

You will need:

• Power BI Desktop installed on your computer or laptop (I am currently running version: 2.67.5404.581 64-bit (March 2019)).
• Access to Microsoft Excel
• This zip file which contains all the assets you’ll need

Let’s get started!

From a pivoted version of the Australian Bureau of Statistics (ABS) age group data, extract below.

Load the data

1. After opening up Power BI Desktop with a blank project, on the home tab click on Get Data, select Excel and navigate to your data file.
2. The Navigator pop-up will appear, select option Sheet1 and load the data.
   

Prepare the data

You will need to do some very minor pre-processing of the data prior to creating visuals.

1. Open the modelling tab.
2. On the right of your screen, your data should have been added to the “Fields” tray. Click on “LGA Official Name” field.
3. On the modelling ribbon, under Properties > Data category, select “Place”.
   
   
4. Notice that there is now a globe in front of the “LGA Official Name” field, this indicates to the program that this field contains information about a place on earth, i.e. this is our “join” column.
5. The mapping visualisation we are going to use takes data in the TopoJSON format, so our next step is to create a TopoJSON for the Local Government Areas (LGAs). We will not go through this in detail here, but LGAs can be downloaded from vic.gov.au as a .SHP file, which can then be converted into TopoJSON using https://mapshaper.org/

Set up the page layout

1. Return to the Home tab.
2. On the right-hand side you’ll notice a panel titled VISUALIZATIONS, click on the roller brush and expand the Wallpaper drop-down menu. Choose a colour—I have chosen “Black 10% Lighter”.
3. Click on the  tool on the Home ribbon menu and a text box will be automatically created on your dashboard. We’ll put our title text here.
   
4. Give your page a heading, I have chosen: “2018 Age Groups by Victorian Local Government Areas” with font Segoe (Bold), size 44, coloured yellow.

Creating the visuals

SHAPE MAP

The first visual we are going to make is using the Shape map function. You can find this function on the Visualisation panel and the icon looks like this: If it’s unavailable, go to File > Options and Settings > Options > Preview Features, then select the Shape Map Visual checkbox. Click “OK”, and restart Power BI Desktop if needed.

1. 1. Click the Shape Map icon, and it will be added to your dashboard. Don’t worry about getting it in the rights spot yet, we can tweak it later.
   2. Drag the “LGA Official Name” field into “Location”.

Save

1. Power BI will populate the map with US states by default, and of course none of the Victorian LGA names will join successfully.
   
2. We need to load in our LGA geometries: switch to the “Format” tab, open the “Shape” drop-down menu, and click the “Add Map” button
   
3. Navigate to your TopoJSON file and open it—after a moment the map should populate with the LGAs of Victoria.
4. Now we are going to style it into a thematic map, based on LGA population.
5. Drag the Population column into “Color saturation”.
   
6. Your map should style up automatically based on the values in the Population column. To style the colour ramp as you like, head to the Format tab and open the “Data colors” drop-down menu.
   
7. Your data should now be styled into a thematic map based on population, and will automatically have included pop-up windows with the LGA name and Population count.
   
8. The final step to complete the map is to enable the drillthrough function, by navigating to the bottom of the “Fields” menu to the “Drillthrough” tab, and flicking “Keep all filters” to on.
   
9. Your map is complete!

SLICER

Next, we are going to add a “Slicer” visualisation. This will allow us to select LGAs by name from a drop-down menu to filter our dashboard on-the-fly. It will act as our primary “drillthrough” control.

1. Click on the Slicer icon under the visualisations menu.
2. We want to populate this list with our LGA names, so drag the “LGA Name Official” column the slicer “Field”
3. Test out the slicer: you should be able to select individual LGA names directly out of your list and have the map update instantly.
   
4. Lastly, we are going to change this to a drop-down menu to save some room. To do this, make sure your Slicer element is selected, and there should a tiny arrow in the top right corner, underneath the three-dot menu. Click this and select “Dropdown”.
   
5. While you’re at it, go ahead and tweak the styling on your Slicer, you will need to adjust some colours to see what you are doing!

That’s a wrap on part 1 of the Getting Started with Power BI series. Click here for part 2 in which we add in our charts to complete our dashboard.