Call us today to see how we can help you be at your best.
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.
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
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.
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.
Drag a card visualization and add the Actual field into the field, which provides an overall total for the actual cost field.
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.
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.
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.
Sign up today to receive our monthly newsletter with tips, Microsoft funding availabilites, upcoming events and industry news.
Take advantage of this hands-on event that will guide you through Microsoft’s security tools and how to protect your business interests.
Sign-up to our monthly newsletter for valuable insights, workshops/events and funding available for your next project!