Creating a business intelligence dashboard with Microsoft Power BI
This tutorial has also published on my personal website.
Let’s assume you are a business owner or manager and your operations generate a large amount of data every day. Days go by and the amount of data increase. You may be miles away but nowadays many know how valuable the data can be to organizations and companies and how the right usage of data can add value to the organization.
You may take a look at those data stored on your computer and see lots of numbers and text in different tables, which seems to be difficult to understand anything. “Data visualization” can help to depict what has happened within the company’s operation and assist the decision makers to grasp “insights” from the data which can help them make more effective and calculated decisions.
Power BI is a robust tool that have made it easy to connect to different types of data sources and import the data to the model. You can create relationships between data tables and make queries to provide necessary manipulations in the data. These changes are not usually done for the imported data, but are to create new tables, columns or measures. Power BI uses the DAX language which is similar to what is used in MS Excel.
In this practice we will be using a complaints dataset from an insurance company. We will create a BI dashboard to provide information and insights laid out in that dataset. This dataset was taken from EnterpriseDNA Power BI challenge 6. The main objectives in this challenge is to provide:
- The ability to see status changes and when they happened
- Complaints broken down by the dimensions
- Client Satisfaction
- Worst offending brokers
Also the data provider prefers to have the insights which belongs to no older than two years ago.
Dataset
The dataset consists of a twelve different tables, including a table named “Complains Data” which is the main table and includes complaint ID, date, broker, customer, client satisfaction and more columns. Each dimension has a separate table that includes data regarding that dimension. For instance we have customers table, brokers, complaint status, regions, etc. Since each complaint’s status may be changed over the progress period, there is a separate table that includes the complaint IDs and the dates that their statuses has been changed.
Relationships
The dataset is almost cleaned and does not need much of manipulations. But in order to calculate some variables for some of the data, we need to create new columns and measures after importing it into the Power BI. First of all we need to review the dataset and understand how tables are related to each other and then create the right relationships and to make sure Power BI has created the correct relationships initially.
Calculations
In order to calculate the duration for each complaint status, we will need the Power BI to check the status dates for each complaint ID and return the number of days each status lasted. We will also need the number of times that status has changed for each complaint ID, which we can have it by using below formula:
StatusDuration =var _CurrentTaskTime = CALCULATE( SELECTEDVALUE('Status History Data'[StatusDate]))var _PreviousTaskTime = CALCULATE( MAX('Status History Data'[StatusDate]), ALLEXCEPT('Status History Data', 'Status History Data'[ComplaintID]), 'Status History Data'[StatusDate] < _CurrentTaskTime)var _timeDifference = IF( ISBLANK(_PreviousTaskTime), 0, DATEDIFF(_PreviousTaskTime, _CurrentTaskTime, DAY))return _timeDifference
Above, we allocate a variable called CurrentTaskTime to store the date for the selected value, and another variable called PreviousTaskTime to grab the maximum date just before the current date for the selected complaint ID, which is set to be smaller than current date. Eventually, the difference between two variables is measured and returned as the output value.
And for the number of status changes:
Number of Status Changes = CALCULATE(COUNT([ComplaintID]),FILTER('Status History Data', [ComplaintID] =EARLIER('Status History Data'[ComplaintID])))
Visualization
Below page is the dashboard’s first page:
If you have numeric values in a column it is pretty straight forward to make conditional formatting in the tables. But what if there are text values and we want to have formatted table based on the text values?
For example, Based on CompletionTime I made a new column to indicate if each complaint is completed or still in progress. I also added a conditional column so that if the value is “Completed” put number 2 and if the value is “In Progress” put number 1. Now based on the numbers I can format the complaint status to highlight yellow if in progress and blue if the complaint is completed.
StatusCode = IF([Complaint Status] = "Completed", 2, 1)
I dedicated the second page to show insights about client satisfaction:
In the line chart in top of the page, I visualized the change of client satisfaction over the course of time. For this, I had to represent the average percentage of satisfied and not satisfied clients in each year. So I created two measures for each line as below:
Average Not Satisfied = var TotalNSA = CALCULATE( COUNT('Complains Data'[ID]), 'Complains Data'[Client Satisfaction] = "NSA")var TotalID = COUNT('Complains Data'[ID])return TotalNSA/TotalID
I did the same for satisfied customers as well.
Note: If you are using the ArcGIS maps in your report, please be aware that currently those maps will not be shown when you publish the report on the web. You can use filled map instead till Microsoft supports the ArcGIS maps in published reports.
The third page is dedicated to complaints timeline and what can better visualize the tasks over the course of time than a Gantt chart? You can add the Gantt chart visual directly from Power BI via “Get more visuals” menu.
The labels on the top are being changed and show the information associated with the selected complaint ID. In Power BI, in order to enable a label to show a specific value associated with the selected filter, you will need to define a measure to do so. For instance, below code will create a measure that shows the Client Satisfaction associated with the selected Complaint ID and if no ID has been selected, it will show “Not Selected”.
SelectedClientSatisfaction = IF( ISFILTERED('Status History Data'[ComplaintID]),SELECTEDVALUE('Complains Data'[Client Satisfaction]), "Not Selected")
Finally, we will create a cover page for your dashboard and link any pictures to any page in your report, or even a web page, using Action section in “Format Image” section.
Finally, you can check the live published map via this link or below:
