Building Your First Power BI Report — A Step-By-Step Approach
Don’t worry, I won’t assume that you’re a techie or someone with little experience in tech, so let’s start from scratch.
I believe most of you want to either start out your career in the data space as a Power BI Analyst or want to switch careers. Either way, I’ll be giving you a step-by-step approach I adopted when building my first Power BI report. Let’s get started!
Not too fast…you need to know what Power BI is... So what is Power BI. Power BI is a business intelligence tool created by Microsoft for data analysis and data visualization functions.
The demand for highly skilled Power BI Analysts is currently high. As an Enthusiast, you can use this opportunity to build your skillset around Power BI and eventually get a job role as a Power BI Analyst. Yes! Power BI Analysts are earning pretty well. According to PayScale and Indeed, average Power BI professionals are earning 117,000 USD per year.
You also need to put into consideration what it entails to become a Power BI Analyst. You can do this by asking yourself if analyzing and getting information is something you have great interest in. If yes, then you can proceed. To learn more about what it takes to be a Power BI Analyst, you might want to check out this article.
In 7 steps, I’ll introduce you to how you can get started with Power BI:
I will be considering a virtual company that wants to get insights from their sales data. The company has a budget excel spreadsheet. From this budget data, you will be able to see how the budget has been structured for each product category, sub-category, and product name. It is also showing the timeline of the data by years, months, and days.
I will be using the AdventureWorks excel data containing quite a number of spreadsheets for this analysis and visualization project. These spreadsheets show the performance of the company’s sales and other dimensional data like Calendar, Customer, Sales, Product, and Territories.
However, the company wants to have better analysis and insights from their excel data, hence, the need to employ the use of a business intelligence tool.
For this case study, I’ll be using the Power BI Desktop app for data analytics and proper data visualization. Let’s get started!
- Get Data. Before doing this, you’ll have to download the Power BI desktop app. This app can be used for importing data, transforming data in the Power Query Editor, creating measures and calculated columns, building visuals, and publishing your Power BI report to the Power BI service (cloud).
You can use this link. All you have to do is just click on “Download” and check the required box and then click on “Next” to finish up the download. You can then install it manually on your local PC.
You can now open the Power BI desktop app to import data for analysis and visualization. All you simply need to do is to click on “Get Data” from the environment, and then select the data source you want to work with. In this case, I’ll be working with an Excel file. Note that your data source can be from your Organization (On-premise), from the cloud, or from an online service.
However, you will only be importing the Budget data and the needed spreadsheets from the AdventureWorks data. In this case, you will be importing the Calendar, Customer, Sales, Product, and Territories spreadsheet from the AdventureWorks data.
2. Transform Data. After importing your data into the Power BI desktop app, it is advisable to click on the “Transform Data” button instead of directly loading the data into the desktop app. This will give you the opportunity to make necessary cleaning and transformations in the Power Query Editor before loading it into the Power BI desktop app.
You can then proceed to make changes to the data. Let’s look at the dataset together and see if we can make any form of transformation on the Power Query Editor.
For the Budget data, you’ll have to start by changing the data type of each column to the appropriate one. You will also have to remove some rows at the top of the data and promote the row at the top to serve as Headers. After these steps, some filtering operations on the Category column of the Budget data can then be implemented. This is done by selecting the column to be filtered and choosing the right product categories you want to analyze in your data.
However, in Power BI, data analysis and modeling are always done column-wise, not row-wise. So, it is mandatory to un-pivot the “Date” columns in order to have a particular column for “Date”. This will help in proper timeline analysis of the data. Other operations that can be performed in the budget data are the removal of columns and renaming of columns.
There are little or no transformations to be done on the Sales data and other dimensional data. You might also want to make sure that each column has the right data type. This is very important in creating relationships eventually when building the data model.
3. Load Data. After implementing all the transformations, and cleaning the data, the Power Query Editor can then be closed. In the Power BI desktop app, you can then use the model pane for creating relationships between the columns. This is done so as to enable slicing and dicing of the sales report. It is done by dragging a column from one table to another table on the appropriate column. Relationships can either be a One-to-One relationship, One-to-Many relationship, and Many-to-Many relationship. However, it is advisable to make sure that the relationship is a One-to-Many relationship in order to achieve effective slicing and dicing of data visuals in the report to be built.
4. Create Measures and Calculated Columns. When creating measures and calculated columns, you have to brainstorm on the questions you want to ask from the data. The insights you want to communicate to your colleagues and the general public can also determine the measures and calculated columns you’ll be creating. Let’s get started.
In creating measures and calculated columns, you’ll have to get some understanding of DAX. DAX is an acronym for Data Analysis Expression for interacting with data. It’s a language invented by Microsoft, and it’s sort of an Excel formula on steroids.
From the budget table, there’s a column for Budget Amount. This column will then be used in creating a “Total Budget” measure. There are two different types of Measures, the Explicit Measures, and the Implicit Measures. But for this project, the Explicit measure will be used. Other measures that will be created from the Sales table are “Total Sales”, “Variance” and “Variance %”.
All these measures will serve as key indicators for the sales report that will be visualized eventually.
5. Data Visualization. You can then start visualizing the data. For this case study, a single Sales report will be built showing the summary of the sales performance of the company. The sales report will encompass the performance of sales made in comparison with the budget, and how the key indicators are analyzed by category, subcategory, and product name.
From the top of the report, the key indicators which are the measures created initially will be horizontally arranged. This is done by selecting the Multi-row visual from the visualization pane of the Power BI desktop app for each indicator. With this, you can easily know the total budget made for a particular year, the total sales, the variance, and the variance %. The variance, in this case, is negative because the sales made are less than the Budget amount.
According to visualization best practices, it is better the color code for each indicator icon is constant for all visuals. This is done by selecting the visuals used and formatting the color codes in the Format pane of the visual.
For timeline analysis, a line visual can then be used. Visuals that are good for time intelligence analysis are the column chart, the line chart, and the area chart. The line chart can then be selected from the visualization pane. With this line chart, it is possible to compare the Budget amount and Sales by dragging each measure to the visual. The line chart visual should also be color formatted to their respective color codes for each measure.
A stacked bar chart can then be used in the report for comparing the performance of different product categories by the Sales and Budget measure. This can be done by selecting the visual from the visualization pane. You also don’t want to ignore the color codes for each measure.
A matrix visual is also suitable for examining how the different key indicators are performing by the Product categories, subcategories, and product names. For this, you just have to select the matrix visual from the visualization pane while dragging the measures and other columns to the visual. The formatting of the visual should also be done in such a way that each measure in the visual has a data bar with its own color code. This can be done by selecting the conditional formatting for each measure.
Considering the variance measure, an area chart visual will be suitable in showing the variance analysis. This is done by selecting the area chart from the visualization pane while dragging the variance measure and the date column for time intelligence analysis. The color code formatting can also be done in the formatting pane of the visual.
It is also very important that slicers are utilized in getting a more advanced analysis of the sales report. In this case, the Product category column, Product color column, and month column will be used as slicers for slicing and dicing of the sales report. This is simply done by selecting the Slicer visual in the visualization and dragging the aforementioned columns into it. Take note that each column for each Slicer visual.
In order to have a beautiful and attractive Sales report, you can use the Microsoft PowerPoint environment for designing a proper background. This is where the color code for each measure is determined before importing it into the Power BI desktop app.
6. Publish and Share. Now that you’re done with the visualization of the data, you can now proceed to publish it to the Power BI service. In order to do that, it is required of you to have a Power BI service account. This can be done if only you have a school/work email address. All you need to do is to navigate to the Power BI service website
To learn more about the Power BI service different licenses and signing up for the Power BI service: https://docs.microsoft.com/en-us/power-bi/fundamentals/service-self-service-signup-for-power-bi
You can now sign in and publish the report to the Power BI service. Now that’s on the Power BI service, you can then share it with Clients and Colleagues.
7. Uhmmmm….you can complete this by contributing in the comment section below.
P.S: To have access to the PBIX file, and the datasets used, you can check this Github repository: https://github.com/samietex/Sales-report_Power-BI.git
P.P.S: Please follow me for more content on Power BI, Power Platform, and data stuff generally.
