In an earlier post, we introduced Power BI and looked at what makes a good dashboard. In this article, we’re going to go through the steps for creating reports with Power BI, SQL, and Excel.
How Does Power BI Work? A Brief Overview
Power BI is a Business Intelligence (BI) platform. With Power BI, users can get data, analyze it, and create visualizations that help turn data into actionable information for their business.
The diagram below shows how the process for creating reports in Power BI typically works – we should note that Power BI is very flexible, and there are normally several different ways to achieve something.
The process begins with getting data from one or more sources and creating a dataset that we can use in Power BI as the basis for our reports and dashboards. As part of this process, we can transform the data if needed. This could include cleaning, merging, calculating, or other ways to massage the data.
Once we have a dataset, we can start creating reports. The best way to explain how to do this is through an example. We’re going to use a set of sales data from the fictional Contoso company – if you want to follow along, the PBIX file is here.
The Microsoft documentation on Power BI reports is here.
Step-by-Step Example: Creating Reports with Power BI using Data from SQL and Excel
In this example, we’re going to be creating reports with Power BI using data in SQL Server. The example also shows how we can use Excel to update some forecast data in SQL and have these changes reflected in our Power BI report immediately. This is a really great way to allow non-technical users to update Power BI reports on the fly.
The sample data consists of sales figures for a number of years for Contoso and can be downloaded here.
The report we’ll be creating is shown below. It consists of a primary dashboard view, with a detailed page relating to some forecast information. This aims to answer some typical sales/financial performance questions like:
- What were our sales/costs/profits for different periods across various business areas?
- Which are our top selling products/regions?
- Was our sales revenue above or below our forecast?
Step 1: Get Data from SQL Server
Open Power BI Desktop, click on ‘Get Data’ and then ‘Import Data from SQL Server’.
In the dialog, select the Server and Database (optional). The Data Connectivity mode defaults to ‘Import’ – leave it on this option. (The import option loads the data into the Power BI file, making it quicker to work on, but is not suitable for very large datasets, whilst the Direct Query option has to run queries against the source data each and every time a visualization is refreshed or filter changed).
You can now select the tables in the SQL database that will be used in the report. We’re going to use all of them, so each one needs to be selected. We won’t do any transformations in this example, so go ahead and click ‘Load’.
Once the data has been loaded, you’ll see the tables in the Fields pane on the right-hand side. We also have a blank canvas to work with and the Visualizations pane to select our charts and other visualization types from.
Step 2: Check Data Model
In the background, when the data is being loaded from SQL Server, Power BI checks for relationships defined in the data source (ie foreign key/primary key relationships). If such relationships exist, they’re mirrored into the Power BI data model when you initially load data. This option enables you to quickly begin working with your model, rather than requiring you find or define those relationships yourself.
To view the data model, click on the ‘Model’ tab on the left side. If any relationships aren’t automatically created, you can add them by clicking on the ‘Manage Relationships’ menu button.
We’re going to create a relationship between the Calendar table and the Sales table:
Ok, our data model is all set up now, so we can start creating some visualizations and report filters.
Step 3: Create Visualizations and Report Filters
When creating reports or dashboards in Power BI, it’s often useful to sketch out what you want them to look like first and to work out what type of visualizations you need to convey the relevant information.
For tips and best practice guidance on how to design reports and dashboards, see our earlier article, Power BI Dashboards: Examples & Use Cases.
Let’s look again at the report that we’re trying to build:
The report we’re making is made up of the following:
- Slicers for Year and Region will be positioned in the top right corner of the page in the title band.
- A set of key summary metrics will be displayed as card visualizations across the top of the page
- Bar charts on the left side to display summary counts of sales by channel and sales by product category
- Line charts in the middle of the page to display sales trend by month and discount as a percentage of sale by month
- Finally, at the bottom, we’ll show some more detail in a table and a matrix.
Adding Slicers to a Report
The slicers allow the report view to display subsets of information with simple selections.
To add the Year slicer, click on the ‘Slicer’ icon in the Visualization pane – a placeholder will be added to the canvas. To add the year, expand the Calendar table and check the Year field from the DateKey hierarchy – it will be populated in the Field placeholder. Finally, we only want to see the years 2011, 2012, 2013 in the report, so we need to filter the values in the Filter pane.
Finally, we’re going to change the slicer so that only one year can be selected at a time (ie no multi-select allowed). Click on the Format tab in the Visualizations pane, then expand ‘Selection Controls’ and turn on the ‘Single Select’ option.
The same process can be used to create the slicer for the Region. This time, we’ll use the ContinentName field from the Geography table. Both slicers can be positioned in the top right of the page.
Adding Cards to a Report
Card visualizations are used to display important information in a prominent manner. We’re going to display summary figures like Total Sales, Total Costs, Profit, etc in cards along the top of the page.
Two of the values to display are already in our data model; the other three need to be calculated.
Let’s start by adding the Sales Amount card. Select the Card visualization, and check the ‘SalesAmount’ field.
The same process can be used to add the Total Cost card.
For the other three cards, we need to create some calculated fields. To do this we’ll use the Power BI DAX (Data Analysis Expressions) language to create some custom measures (ie calculated fields).
The first new measure we’re going to create is the Gross Profit figure. In the Fields pane, select the Sales table and then right-click and select ‘New Measure’. The DAX formula bar is displayed below the ribbon, and we can enter our formula. DAX formulas are very similar to Excel formulas. It uses functions, expressions, and any required values or arguments.
For the Gross Profit value, our formula is:
Gross Profit = SUM(Sales[SalesAmount])-SUM(Sales[TotalCost])
Next, we’re going to create the Gross Profit Margin value using the same procedure as above. Our DAX formula, in this case, will be:
Gross Profit Margin = (SUM(Sales[SalesAmount])-SUM(Sales[TotalCost]))/SUM(Sales[SalesAmount])Finally, let’s create the Sales Amount Year over Year variance. This is a more complicated formula, but fortunately, it is one of the pre-defined ‘Quick Measures’ in Power BI. To add it, select the Sales table, right-click and click on ‘New Quick Measure’.
In the Quick Measures dialog, select the ‘Year-over-year change’ calculation. In the dialog box for the ‘Year-over-year change’ calculation, we need to enter the ‘Base Value’ – this is the value that we want to do the year-over-year calculation on, which in this case is the SalesAmount from the Sales table. The ‘Date’, is the DateKey hierarchy from the Calendar table, and the ‘Number of Periods’ is 1 (ie one year).
Once you click ok, the formula will be created and the new measure added to the model. Here is the DAX formula that was created for the ‘Year-over-year change’:
Now that we have our 3 calculated fields (or ‘measures’) in our data model, we can create the remaining cards using the same process as described above. They can be arranged at the top of our page by dragging them and using the alignment tools:
Adding Bar Charts to a Report
Bar charts are good ways to summarize data across a few categories. We’re going to create one to show Total Sales by Channel and one for Total Sales by Product Category.
Click on the ‘Stacked Bar Chart’ visualization. For the ‘Axis’ data field, drag the ChannelName field from the Channel table. For the ‘Values’, we are just going to add one field – the SalesAmount from the Sales table.
That’s all we need to do in terms of data for the bar chart, but we should apply some formatting to make it more effective. One of the tips from our previous Power BI intro article was to keep visualizations as simple as possible so that views can focus on the numbers.
To remove the axis headings (they are not really needed), click on the Format tab in the Visualization pane, then go to ‘Y-axis’ and turn the ‘Title’ option to Off. Do the same for the X-axis.
Next, we’ll change the bar colors to grey. In the Format tab in the Visualization pane, go to ‘Data colors’ and change the ‘Default color’ to grey.
Next, we’ll add data labels to the bars, by turning the Data Labels option in the Format tab to On.
Finally, we can change the auto-created heading to something a bit more readable. Go to the Format tab and in the Title section, edit the Title text. The end result looks like this:
We can make a copy of this and simply change the field in the Axis to be ProductCategory instead of ChannelName.
Adding Line Charts to a Report
Line charts are a good way for us to display sales trend over time.
The procedure for adding a Line chart is very similar to the other visualizations – the basic set-up result in the chart below.
However, for this kind of Sales Summary dashboard, it’s not appropriate to show so many data points – monthly figures are fine. We therefore want to group the data points into buckets on the x-axis. To do this, select the DateKey field, right click and then click on ‘New Group’. In the Groups dialog, set the Group Type to ‘Bin’ and the Bin size to 1.
With a bit of extra cleaning up (removing unnecessary titles) our line chart is ready. We can now make a copy of it to create the second line chart.
The second line chart uses a new measure called ‘DiscountPercentofSales’. The formula for this is shown below:
Adding Tables and Matrix to a Report
Tables are useful to display relatively small amounts of data in more detail. In our example, we’re adding ‘Top 5 Stores by Sales’ and ‘Sales by Category and Location’.
The process for adding Table and Matrix visualizations is exactly the same as for the other types.
For the Top 5 Stores table, the only extra configuration is the filter on the visualization – see below.
Adding a Title to a Report
Our report is looking good so far, but it’s missing something important – a title!
To add titles or other headings or textual information to a report, we can simply add text boxes to the report. For this report, we just have a simple text box for the title, as well as a grey background block to emphasize our title area (which also contains the slicers).
Bonus: Updating Power BI Reports from Excel
In our example, we have created a report based on a set of sales data for the fictitious Contoso company. It is a common use case to add context to reports and dashboards by comparing actual data with the forecast – a key metric for sales teams is “Are we ahead of or behind our target?”.
The data source in our example is a database in SQL Server, which we can assume gets updated from the Contoso financial system. What is the best way to include forecast or target data into the data source of our reports/dashboards?
The simplest (and most robust) way is to add a Forecast table to the sales database and perform regular updates to it as the year progresses. A really easy way to make these updates to the Forecast table in SQL Server is to use the SQL Spreads Excel Add-In.
Adding a new Forecast table to SQL Server
With the SQL Spreads Add-in installed in Excel, we can connect to our Contoso Sales database and create a new table called ‘Forecast’. We can then add or edit data in that table from within Excel.
Once the SQL Spreads Add-in is installed in Excel, we can create our Forecast table. It consists of a Datekey column (the values are just the 1st day of each month) and a Forecast column where we enter the sales forecast values. In the real world, this would be a lot more granular with sales forecast figures for each product area, region, store, etc.
To create the new table in SQL Server, we just need to make sure our data range is formatted as a table and then click on the ‘Create New SQL Table’ button.
A simple dialog comes up asking where we want to create the table, and what mapping changes we want to make (if any).
Click ‘Ok’ to create the table and load the data.
Because we now have a new table in our Sales database which we want to include in the Power BI dataset, we need to re-connect to the data source and load it into the data model.
Updating the Forecast table from Excel
Now that the Forecast table is in SQL Server, any user with the SQL Spreads Add-in can connect to it, and make updates from within Excel.
Once the updates have been made in Excel, they can be saved to the database and the changes will reflect in the Power BI reports – we now need to update these to include forecast data.
Updating the Power BI report to include Forecast data
We can create a separate page for the forecast data and display some simple visualizations like those shown below. Of course, with more granular forecast data we could show forecast variances across products, regions, etc.
Summary
This article has provided step-by-step instructions on creating reports in Power BI; in our example, we created a one-page report (similar to a dashboard) to display sales data.
We used a SQL Server database as the data source, and with the addition of the SQL Spreads Add-in for Excel, came up with a neat way of updating our sales forecast data in the report from the comfort of Excel, whilst retaining the advantages of storing the data in SQL Server.
In a previous article, we identified the reasons why SQL and Excel work well together and can form the foundation of your data management system and greatly enhance the way you do business. If you add Power BI to your stack, you can take it one step further and create great ways to further visualize and analyze your data.
If you haven’t already used the SQL Spreads Add-in for Excel, download a trial here.
