If you haven’t heard of Power BI yet, it’s Microsoft’s cloud-based BI service and desktop app that allows you to take data and allows you to build interactive reports and dashboards with a variety of visualizations to aid in data discovery and data preparation. If you would like a more in-depth introduction to Microsoft Power BI go check out an article we wrote on it .
Due to the fact that not everyone will be working on a Windows machine, this blog article has been structured to use the Power BI online service to build the report. Unfortunately, as the time of writing this blog, the samples provided by Microsoft are only available online and cannot be accessed via the desktop app. However, if you are interested in test driving the desktop app you can head over to the Microsoft Store on your PC and download the latest copy of Power BI Desktop.
Importing Data
Power BI supports most popular data sources. To view all the supported data sources, simply navigate to the “Home” ribbon tab and select “Get data” in the Power BI Desktop app. In the get data window you can scroll through all the various connectors and find the connector you would like to use.
For the simplicity of this blog, we are going to be importing sample data supplied from Microsoft. First, let’s navigate to https://app.powerbi.com and select “My workspace” (free) or a workspace of choice (requires Power BI Pro license). In the workspace, select “Create” in the top right-hand corner of the workspace and select dataset. Under the “Create new content” section select “Get” under files. Select samples and then select the “IT Spend Analysis Sample” and select connect.
Once connected, you will notice under the reports tab in your workspace that there is a new report created called “IT Spend Analysis Sample.” Click on the report to launch it. Once you’ve launched the report, you should see a “Edit report” button in the top toolbar, click it.
If you’ve edited a Power BI report before, this should look relatively familiar to you. If this is your first time, there are a few key areas of the UI you should be introduced to. I’ve broken the major areas down and highlighted them in the screenshot below.
-
Report page: This is where you are going to add all your report visualizations
-
Filters pane: This is where you can drag and drop data fields that will allow you to filter visualizations, the current page, or all pages by data points in your field
-
Visualizations: This pane includes all the built-in visualizations in Power BI and custom visualization
-
Fields: This is where you can access your dataset tables and fields when adding the data to report visuals
Building your first page
To begin, first click on the plus sign “+” in the report tabs area to create a new page. To rename your newly created page, simply double click on the page table and the page name will be highlighted, then you can enter the new name. For our case, lets call it “Country and Region Analysis.” To not overly complicate this blog, we will only be creating four distinct different visualizations using our sample dataset.
1. Stacked column chart
In the visualizations pane, drag in the stacked column chart (hint: to find out the name of each visual, simply hover over each visual to see the tool tip) into the report. Once in the report, select the visual and notice how in the visualizations pane, the fields change for the selected visualization, as highlighted in the screenshot below.
What we would like to build is a visualization that shows us our actual cost over time for each region. To do this, expand the Fact table in the fields pane and drag the Actual field into the value field of the selected visual. In the CountryRegion table, drag the Country/Region field into the legend field area of the selected visual. Finally, drag the Month field from the Date table into the axis field area of the visualization. The visualization fields should now look like the screenshot below.
For the next visualizations, I’m going to not go into as much detail as I did in the previous visualization step, but instead just highlight what visualization we are going to add plus the required fields.
2. Map
For the next visualization we are going to add a map visualization to report and drag the Country/Region field into the location of the visualization. Drag the Actual field into the size of the visualization.
3. Pie chart
Next, drag in the pie chart visualization. For this visualization, we would like to highlight a break down (percentage) of business areas that make up the actual cost. To do that, add the Business Area field into legend and the drag the Actual field into values.
4. Card
Drag a card visualization and add the Actual field into the field, which provides an overall total for the actual cost field.
5. Filter
We have added all our required visualizations, what would be nice is to be able to filter our data by Region/Country and IT Area. To do that, we are going to drag two slicer visualizations into the report and drag Region/Country field into the one and IT Area field into the other. One thing you might notice is these slicers are checkbox lists. To take up less space we can change it from a list to a dropdown. To do that, hover over the slicer and click the arrow to select dropdown. Do this for the other slicer as well.
What next?
Congratulations, you have built a simple report page with sample data. To publish your changes, simply click save. You can now share this report with anyone, but be aware that you have to have a Pro license to do this (or a trial of the Pro license). There a lot of further tweaks we could make to this report, but in the essence of time, I decided not to go over them. I’ve made some simple bullet points below to provide some ideas of ways to add to this report.
- Edit the report formatting (adding more distinct titles, change font sizes and colour)
- Changing theming colour for visualizations
- Creating district divided regions with borders (use a rectangle shape)
- Add a title to the report
- Create an app from the report for easier distribution
I’ve included a screenshot below of the final report. Although not pretty, it provides a brief idea how simple building reports can be. One of the toughest parts of building a report from data is knowing what is the best way to convey that data. It helps to have a question you are trying to answer or you have a story you would like to tell to the user.
Written By:
Colby Timm
Colby is a Software Developer and Data Analyst who works with the Power Platform, building Power BI reports and dashboards, and more recently building healthcare related PowerApps. Colby also has experience developing in SharePoint and developing web applications in .Net. He has a Microsoft certification in Power Platform Fundamentals.
More By This Author
