At Linear Squared, we use a variety of historical or simulated data to develop complex algorithms that generate action plans which address various business problems of our clients. When doing so, we sometimes need to create customized, quick and dirty dashboards with data-driven insights to communicate intermediate findings and Key Performance Indicators (KPIs) to non-technical clients. There are many tools for creating such dashboards. At Linear squared we often use Microsoft PowerBI and RShiny for this purpose. This post will walk you through a few easy steps to create your very own simple dashboard using PowerBI.
What is Power BI: PowerBI is a Business Intelligence (BI) tool that lets you tell a story via visualizing data. It is owned by Microsoft. We can use the free PowerBI desktop version to create visualizations and publish a report to powerbi.microsoft.com using a Microsoft account. Multiple reports can be included in a single dashboard.
Advantages of PowerBI dashboards
- Ability to share the dashboard with a large audience anytime using a public or private link
- Ability to see all the visuals at a glance
- Ability to slice and filter data without changing the underlying data or existing visualizations
- Ability to include multiple pages with different page level filters
- Ability to resize all the pages and create the dashboards in different sizes
- Ability to embed dashboards into other applications
- Ability to show multiple reports, created using different databases
Difference between “dashboards” and “reports”.
Given below is a good comparison between static reports and dashboards published by Microsoft.
|Pages||One page||One or more pages|
|Data sources||One or more reports and one or more datasets per dashboard||A single dataset per report|
|Available in Power BI Desktop||No||Yes, creators can build and view reports in Desktop|
|Subscribe||Can subscribe to a dashboard||Can subscribe to report pages|
|Filtering||Can’t filter or slice||Many different ways to filter, highlight, and slice|
|Featured||Can set one dashboard as your “featured” dashboard||Cannot create a featured report|
|Favorite||Can set dashboards as favorites||Can set reports as favorites|
|Set alerts||Available for dashboard tiles in certain circumstances||Not available from reports|
|Natural language queries||Available from dashboard||Not available from reports|
|Can see underlying dataset tables and fields||No. Can export data but can’t see tables and fields in the dashboard itself.||Yes. Can see dataset tables and fields and values.|
|Customization||No||In Reading view, you can publish, embed, filter, export, download as .pbix, view related content, generate QR codes, analyze in Excel, and more.|
Note. Reprinted from: Dashboards in the Power BI service – Power BI. [online] Available at: https://docs.microsoft.com/en-us/power-bi/service-dashboards [Accessed 5 Nov. 2018]
Now let’s learn how to create a dashboard using PowerBI. We can follow the following simple steps.
- Create a report with visualizations using power bi desktop.
- Publish the report
- Create the dashboard and share
1) Create a report with visualizations using power bi desktop.
You can read the data file using the “Get Data” option in the ribbon. You can use excel, csv, text, JSON, SQL or any other standard data type.
After reading the data, you must check for all the variable types and column headers. To do that, you can hit the “edit queries” option in the ribbon. In a new window, you’ll see all the variables and column names. If the column names are not in order, there is an option called “Use First Row as Headers”. It will make the first row as column headers.
And also, in the top left of each column, you can see the data type of the column. You can right click on it and change the data type. After doing the changes to the data set, close and apply the changes.
Now your data set is ready to do the visualizations.
- In most of the graphs, there is an option called “values” to fill. The variable that you need to show in the graph should be dragged and dropped to the “values” section.
- And, the “axis” section requires the variable the x axis needs to show.
- The “legend” section should be filled with one of the categorical variables.
- Let’s go with the very basic graphs.
This is one of the main visuals in PowerBI. You can filter out your visualizations using the “slicer”. The slicer works only for the corresponding page. And you can select the type of the slicer as follows.
b) Bar chart
- Stacked bar chart
- Clustered bar chart
c) Pie chart
The Histogram visual is not available in PowerBI desktop straight away. Hence, you should import the custom visual for Histogram from the PowerBI store. There are plenty of other visualizations in the PowerBI store.
Hit on the “Add” button to download the custom visual to the PowerBI desktop.
Then you can drag and drop the variables into the blanks to show the histogram.
e) Line chart
To display maps in your report, the locations should be in the standard names. Here I have used the Bing map, so the names of the districts in Sri Lanka should be in the way (Spellings) as they are in the Bing map. Or else you can have Latitudes and Longitudes in two different columns and show the exact location.
Now your report is ready to go!
** You can try out all the other custom visuals in the PowerBI store. Have fun!
2) Publish the report
To publish your report, you should save your PowerBI report and sign into http://powerbi.microsoft.com using your Microsoft account.
You can select the destination as “My workspace” and continue.
You published your work!
3) Create the dashboard and share
Have a look at the reports you published. Go to http://powerbi.microsoft.com. All your published reports are there in the “Reports” section.
Open the report, so you can see all the reports in different pages (if you had any). You can create a dashboard by clicking the “Pin Live Page” option as follows. You can resize and add multiple pages to a dashboard.
Then go to My workspace -> Dashboards to see the new dashboard you created.
Try more visualizations using different colors to make your dashboard nice. Share the dashboards with others.