{"id":196,"date":"2020-09-08T23:24:41","date_gmt":"2020-09-08T19:54:41","guid":{"rendered":"http:\/\/mahshad.pro\/?p=196"},"modified":"2024-09-02T22:05:27","modified_gmt":"2024-09-02T18:35:27","slug":"creating-a-business-intelligence-dashboard-with-microsoft-power-bi","status":"publish","type":"post","link":"https:\/\/mahshad.pro\/?p=196","title":{"rendered":"Creating a business intelligence dashboard with Microsoft Power\u00a0BI"},"content":{"rendered":"\n

This tutorial is also published on Medium.com<\/a><\/p>\n\n\n\n

Let’s assume you are a business owner or manager and your operations generate large amount of data every day. Days go by and the amount of data increases. 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.<\/p>\n\n\n\n

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. \u201cData visualization\u201d can help to depict what has happened within the company\u2019s operation and assist the decision makers to grasp \u201cinsights\u201d from the data which can help them make more effective and calculated decisions.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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<\/a>. The main objectives in this challenge is to provide:<\/p>\n\n\n\n

    \n
  1. The ability to see status changes and when they happened<\/li>\n\n\n\n
  2. Complaints broken down by the dimensions<\/li>\n\n\n\n
  3. Client Satisfaction<\/li>\n\n\n\n
  4. Worst offending brokers<\/li>\n<\/ol>\n\n\n\n

    Also the data provider prefers to have the insights which belongs to no older than two years ago.<\/p>\n\n\n\n

    Dataset<\/h4>\n\n\n\n

    The dataset consists of a twelve different tables, including a table named \u201cComplains Data\u201d 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\u2019s 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.<\/p>\n\n\n\n

    \"\"
    complaints dataset <\/figcaption><\/figure>\n\n\n\n

    Relationships<\/h4>\n\n\n\n

    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.<\/p>\n\n\n\n

    \"\"
    Making relationships between related tables <\/figcaption><\/figure>\n\n\n\n

    Calculations<\/h4>\n\n\n\n

    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:<\/p>\n\n\n\n

    StatusDuration =\nvar _CurrentTaskTime = CALCULATE( SELECTEDVALUE('Status History Data'[StatusDate]))\nvar _PreviousTaskTime = CALCULATE(\n   MAX('Status History Data'[StatusDate]),\n   ALLEXCEPT('Status History Data', 'Status History Data'[ComplaintID]),\n   'Status History Data'[StatusDate] < _CurrentTaskTime)\nvar _timeDifference =\n   IF( ISBLANK(_PreviousTaskTime),\n   0,\n   DATEDIFF(_PreviousTaskTime, _CurrentTaskTime, DAY))\nreturn _timeDifference<\/code><\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    Above, we allocate a variable called CurrentTaskTime<\/code> to store the date for the selected value, and another variable called PreviousTaskTime<\/code> 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.<\/p>\n\n\n\n
    \"\"
     Calculating the number of days each status lasted <\/figcaption><\/figure>\n\n\n\n
    And for the number of status changes:<\/p>\n\n\n\n
    Number of Status Changes = CALCULATE(\nCOUNT([ComplaintID]),\nFILTER('Status History Data', [ComplaintID] =\nEARLIER('Status History Data'[ComplaintID])))<\/code><\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    Visualization<\/h4>\n\n\n\n
    Below page is the dashboard\u2019s first page:<\/p>\n\n\n\n
    \"\"
     First page of the dashboard <\/figcaption><\/figure>\n\n\n\n
    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?<\/p>\n\n\n\n
    For example, Based on CompletionTime <\/code>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 \u201cCompleted\u201d put number 2 and if the value is \u201cIn Progress\u201d 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.<\/p>\n\n\n\n
    StatusCode = IF([Complaint Status] = \"Completed\", 2, 1)<\/code><\/pre>\n\n\n\n
    \"\"
     Conditional formatting window <\/figcaption><\/figure>\n\n\n\n
    I dedicated the second page to show insights about client satisfaction:<\/p>\n\n\n\n
    \"\"
     Second page of the dashboard <\/figcaption><\/figure>\n\n\n\n
    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:<\/p>\n\n\n\n
    Average Not Satisfied = var TotalNSA = CALCULATE( COUNT('Complains Data'[ID]), 'Complains Data'[Client Satisfaction] = \"NSA\")\nvar TotalID = COUNT('Complains Data'[ID])\nreturn TotalNSA\/TotalID<\/code><\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    I did the same for satisfied customers as well.<\/p>\n\n\n\n
    Note:<\/strong> If you are using the ArcGIS<\/strong> 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<\/strong> maps in published reports.<\/p>\n\n\n\n
    The third page is dedicated to complaints timeline and what can better visualize the tasks over the course of time than a Gantt chart<\/strong>? You can add the Gantt chart visual directly from Power BI via \u201cGet more visuals\u201d menu.<\/p>\n\n\n\n
    \"\"
     Complaint timeline is visualized within a gantt chart <\/figcaption><\/figure>\n\n\n\n
    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<\/code> associated with the selected Complaint ID<\/code> and if no ID has been selected, it will show \u201cNot Selected\u201d.<\/p>\n\n\n\n
    SelectedClientSatisfaction = IF( ISFILTERED('Status History Data'[ComplaintID]),\nSELECTEDVALUE('Complains Data'[Client Satisfaction]), \"Not Selected\")<\/code><\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    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 \u201cFormat Image\u201d section.<\/p>\n\n\n\n
    \"\"
     <\/figcaption><\/figure>\n\n\n\n
    Finally, you can access the live published dashboard via this link.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
    How to create a business intelligence dashboard using Microsoft Power BI<\/p>\n","protected":false},"author":1,"featured_media":538,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[28,40],"tags":[42,32,43,41],"class_list":["post-196","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","category-works","tag-business-intelligence","tag-data-visualization","tag-dax","tag-power-bi","has-thumbnail"],"_links":{"self":[{"href":"https:\/\/mahshad.pro\/index.php?rest_route=\/wp\/v2\/posts\/196","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mahshad.pro\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mahshad.pro\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mahshad.pro\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mahshad.pro\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=196"}],"version-history":[{"count":7,"href":"https:\/\/mahshad.pro\/index.php?rest_route=\/wp\/v2\/posts\/196\/revisions"}],"predecessor-version":[{"id":540,"href":"https:\/\/mahshad.pro\/index.php?rest_route=\/wp\/v2\/posts\/196\/revisions\/540"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mahshad.pro\/index.php?rest_route=\/wp\/v2\/media\/538"}],"wp:attachment":[{"href":"https:\/\/mahshad.pro\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=196"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mahshad.pro\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=196"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mahshad.pro\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=196"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}