This blog post is part two of a two-part summary of my training workshop in conjunction with BusinessOne, a student-run pro-bono consulting club based at the University of Melbourne.
The workshop was aimed at helping the project teams at BusinessOne learn basic data cleaning techniques using Microsoft Excel, as well as introducing Microsoft Power BI as a tool to build interactive dashboards that communicate the key insights in a data set.
Today’s blog post will be focusing on the second half of the workshop, which is building a dashboard from scratch with Power BI using the transaction data set we have cleaned from the first half of the workshop.
To make it easier to follow along, I would recommend first going over part one. Alternatively, you can catch the full recording of the workshop below.
All the materials used for the workshop can also be found on my GitHub here.
Introduction
Power BI is an interactive software developed by Microsoft for the main purpose of data visualisation and Business Intelligence.
Input data can come from a variety of sources, including from a database, webpage, or structured files such as an Excel spreadsheet, a CSV file, and so on. These data can then be visualised via different visuals to communicate insights.
For the purpose of this workshop, we will be reading our data from an Excel spreadsheet, create visualisations using built-in Power BI visuals, as well as publish the report online once we are done.
Along the way, I will also introduce the Power BI user interface as well as briefly describe some of the things to look out for when first starting out as a beginner.

Get data
The first step to building any Dashboard is getting the data in. Simply choose from a range of options including Excel, SQL Server, CSV file and more under the get data icon.

Here, because our transaction data set is in the form of an Excel spreadsheet, we will proceed with the Excel workbook option.

In order to build the dashboard that we want, we need to select both the customer and transaction tables. Once selected, hit the load button to load in the Excel spreadsheets.
Power BI user interface
Let’s now discuss the Power BI user interface. There are 3 main tabs in Power BI:
- Report
- Data
- Model
Firstly, the report tab. This is the tab that we will spend the majority of our time on when building our dashboard. This is where we can drag and drop different visuals that we wish to create. Our final product will reflect exactly what is on the report page.

Next, we have the data tab. The data tab shows all the tables that are currently loaded in Power BI and available to be used. Here, we have two data tables: customer and transaction, which we will be using later on to build our dashboard.

Last but not least, the model tab. The model tab gives us a view of the relationship between the tables. Here, we can see a many to one relationship between the transaction and customer tables, connected on the loyalty card number field.
In other words, this means that loyalty card number is unique to each row in the customer table but repeated in the transaction table. This makes sense because a customer can shop for items multiple times throughout the year and therefore show up multiple times on the transaction table, whereas the customer table only keeps a single record (row) for each unique customer.


Creating your first visual

Power BI is very similar to PowerPoint in that they have similar drag and drop feel to them. We can see a range of visuals above that are readily available in Power BI: cards, pie chart, table, and bar chart.
To me, the best way to learn Power BI is to play around with the visuals and the ways that you can customise them to your own needs. To help you get started, I will demonstrate how to create a simple card visual i.e. one of the summary metrics seen below.

First, we need to select a visual. In this case, we want a card visual. Then, simply drag a field from the available tables on the right hand side into the fields section. This will automatically populate the card visual and show the value of $1.93 million, which is the total sales generated by the retail store in that particular financial year.

Once the visual is populated, you will get the option to customise them such as changing the colour, font size, value decimal places, and so on. As I said before, the best way to learn this is simply by experimenting with the different options and see what each of them does.

DAX formulas
There are times when a calculation is not as simple as a drag and drop like calculating total sales. Take best seller product as an example, where we want the product name with the highest quantity sold.
Because we are considering across multiple fields in our calculation namely product name and quantity sold, using only one of the field names is not sufficient. This is where DAX comes in.
DAX is short for Data Analysis Expressions. They are quite similar to Excel formulas in that they are mainly used to define custom calculations using built-in functions.
To demonstrate this, let’s use DAX to calculate best seller product.
Best Seller = TOPN(1, VALUES('transaction'[Product Name]), CALCULATE(SUM('transaction'[Product Quantity])))
The formula essentially says take a sum of all the quantity sold for each product, rank them from highest to lowest, and finally take the one at the very top. This will give us our best seller product.
Filters
Next, we are going to learn about filters. As the name suggests, filters allow us to only display data that meet certain conditions.

Suppose the example above, a table which shows the top 50 customers with the highest recorded sales. This is probably one of the most straightforward use cases for a filter.
All we need to do to apply a filter is first select the table, navigate to the filters pane, and apply top N filter sorted by the total sales field. See image below for reference.

Slicer and slicer interactions
Let’s now talk about slicer. A slicer, similar to a card as well as all the other charts that we have seen above, is a type of visual in Power BI. It allows the user to filter the data on charts that are connected to that particular slicer.
Here, we have a slicer of the different product names. Say if someone was to select a particular product, this will then change both the clustered column chart as well as the time series plot in order to reflect the products that are selected.


One thing to keep in mind though when working with slicers is slicer interaction. A slicer will interact will all the visuals on the same reports page unless turned off manually.
This can be found under the format section above.

Grouping visuals
Grouping together visuals has two benefits:
- Keep the workspace neat which in turn enables us to find a particular visual on the page quickly and easily
- Hide or move a group of visuals simultaneously

While there is no hard and fast rule on how you should group your visuals, I personally like to group them according to their position or category on the dashboard.
For instance, here, I have grouped all of my visuals into 3 separate and properly labelled buckets:
- Summary metrics
- Customer demographics
- Sales breakdown
This just makes things easier if and when I need to go back to check or change a particular visual on the report.

Publishing the report
Finally, after all the hard work is done and once you are happy with how your report looks, you can now publish and share the report.
Simply navigate to the home section, hit on the publish icon, and follow the prompt until the report is published online. Once it is online, you can share it with different user groups and get feedback.

In summary, Power BI is an extremely powerful and popular tool particularly for business intelligence purposes. It is not only easy to use but when done properly, can also communicate meaningful insights about the data to various stakeholders.
To supplement this article, I highly suggest watching the full video on my YouTube channel where I will demonstrate in more detail each step that is covered here.
If you found any value from this article and are not yet a Medium member, it would mean a lot to me as well as the other writers on this platform if you sign up for membership using the link below. It encourages us to continue putting out high quality and informative content just like this one – thank you in advance!
Don’t know what to read next? Here are some suggestions.
Every Beginner Needs to Know These Data Cleaning Techniques in Microsoft Excel