Creating Dashboards with Power BI: A Beginners Tutorial Case Study

7 min readMar 22, 2023

In today’s data-driven world, businesses are under increasing pressure to make sense of the vast amounts of information they collect. This is where business intelligence tools like Power BI come in, providing organizations with powerful tools for analyzing data and creating visually compelling reports. In this blog post, we’ll take a deep dive into a case study of a company that used Power BI to create a dashboard that revolutionized their reporting and decision-making processes. We’ll explore the goals of the project, the data sources used, the dashboard design, and the results achieved. Whether you’re a business owner, analyst, or data enthusiast, this case study is sure to provide valuable insights into the power of Power BI.

In this case study, we’ll be exploring a retail store dataset consisting of several columns such as Item Number, Category, Product Name, Week ID, Week Number ID, Average Inventory On Hand ($), In-Stock %, and Sales ($). The goal of this analysis is to use Power BI to visualize the data and gain insights into the store’s sales and inventory trends. By creating interactive and dynamic visualizations, we can easily identify patterns and correlations within the data, which can help us make data-driven decisions to improve the store’s performance. Let’s dive in and see what insights we can uncover!

Get data from https://github.com/kaneezfatima001/Sales-Data-Power-BI

Open the excel file called “SalesData.xlsx”, select the In-Stock Data sheet from the left side of the window and then click on Transform Data. This should bring you to the Power Query Editor. The Power Query Editor allows you to navigate through and transform the data.

Great! Now that we have loaded our data, let’s get to cleaning it.

When we view the In-Stock data file, the first thing that we notice is that the first row in the file as well as Column 9 and 10 have null values. Values like these slow down our model’s overall performance so lets clean that up. You can delete the rows and columns by selecting the “Remove Rows” and “Remove Columns” options respectively.

Once we do this, we may now notice that the first row of our data actually consists of our column name. So let’s change that by selecting the “Use first row as headers option”.

Click “Close & Apply” in the top left corner of the home section in the Power Query Editor to make sure our changes are applied.

You can even change the data type of a column easily by clicking on the data view icon on the left of the window.

Cleaning our data can be a long and tedious process. Mentioned above are a few basic ways of dealing with data that have a few obvious anomalies but there is a lot more to it.

For the sake of this tutorial, I have uploaded a “ready to use” file called “PowerBI Tutorial.pbix”. Please refer to the link attached above for the same. Once that has loaded, we can get to the fun part — Visualizing our data.

Creating a report

In this tutorial, we will learn to create a few charts and understand how you can analyze data.

Visualizations:

Bar Charts: Now that our data has loaded we can view it in the Fields pane to the right of our screen. We can select our desired visualization from the visualization pane and go back to the fields pane to select the data that we want to analyze. Let’s start by analyzing the products in our inventory and its sales. Select the Average Inventory On Hand ($), Sales ($), and the Category fields.

Congratulations on creating your first chart in Power BI!

This bar chart compares the sum of the Average Inventory for every Category. However, a logical way of studying this would be to compare the Average value of our Inventory and the Average Sales for every category. We can do this by changing it’s values from Sum to Average in the Visualization pane.

Using the same logic and fields, you can drag and drop different types of bar charts from the visualization pane and display the data in the form of a Stacked bar chart, Stacked column chart, Clustered bar chart, etc as shown below.

Line Charts: Let’s visualize our data using a line chart. Select the line chart from the visualization pane and select the Sales field in the Fields pane. Drag and drop the Week ID into the Axis section of the visualization pane as shown below. This shows us the sales made with respect to the week ID. Further we can gain insights on the availability of products by comparing our inventory with our sales. To do this, drag and drop the Average Inventory field onto the secondary axis section in the visualization field. You can also click on your chart, and then change it to a Area chart or a Stacked area chart from the visualization pane.

Pie/Donut Charts: Pie charts are quite well known however when it comes to displaying a lot of data, it’s recommended that one does not use a Pie chart simply because it can be difficult to identify tiny differences between categories. For instance: clearly determining which category takes up more area.

To create a pie/donut chart, choose the pie/donut visualization and the appropriate categories to display data as shown below.

KPIs: Key performance indicators (KPIs) are a measure of the company’s performance and evaluates it’s success. We can display this in Power BI using the card or gauge option from the visualization pane. While using the KPI option, we will need to drag the week ID onto the trend axis in the visualization pane in order to view the weekly trends behind the KPI value.

Deleting a chart: To delete a chart, simply click on it and press delete.

Adding a new page: To add a new page, click on the ‘+’ icon at the bottom of the window.

Now that we have learnt how to visualize our data, we can consolidate everything and make a dashboard.

Creating a dashboard

To make the dashboard, copy paste all the visualizations that we have created so far onto the same page as shown below.

here are some potential insights that could be derived from analyzing the sales data provided:

Revenue: The first card in visualization shows the total sum of sales which shows the particular company is generating a great amount of revenue.
Sales by category: A visualization showing the total count of sales by product category, such as moniters, Tablets and comuters could help the company identify which categories are driving the most revenue.
Sales over time: A line chart showing sales trends over time, broken down by week, could help identify patterns and fluctuations in sales. This information could be used to adjust pricing, promotions, or inventory levels.
Inventory management: A visualization showing the average inventory on hand for each product, along with the sales, could help the company manage its inventory levels more effectively. This information could be used to identify slow-moving products or to adjust inventory levels based on seasonality or demand.
Product performance: A pie visualization showing the top-selling products by category, could help the company make data-driven decisions about which products to promote or which ones may need to be discontinued.

By using Power BI to create dynamic and interactive visualizations, companies can gain insights into their sales data and make data-driven decisions to improve their performance.

Customization

Now that our dashboard is ready, we can configure our visualizations by exploring the various options available in the Format section of the visualizations pane.

In conclusion, creating a simple sales dashboard in Power BI is a straightforward process that can provide valuable insights into your business. By following the steps outlined in this tutorial, you should now have a good understanding of how to connect to and transform your data, create visualizations, and build a dashboard. Remember to experiment with different chart types and filters to find the best way to represent your data. With Power BI, you can easily share your dashboard with others and keep track of key performance indicators in real-time. I hope this tutorial has been helpful in getting you started on your journey with Power BI dashboards.