In this tutorial we are going to create a dynamic dashboard that analyses a production business where we have our data scattered in multiple files. With many products, employees and locations, the audits are reviewing the quality of production for defects and fatal errors taking random samples and recording their findings.
You can Download the Exercise Files and follow along by clicking here below
Table of Contents
What is Power BI?
Power BI (Business Intelligence) allows us to do four things:
Get the data
When you have your business data scattered among different applications or platforms and you want to analyze it all together, you need to first to bring this data under one single roof. So, Power BI allows you to get all the data together in one single application, whether this data is in multiple Excel Files, Text files, CSV, Access data base, SQL server, Microsoft Azure, Google Analytics…etc.
Cleaning and Transforming the data
Most of the time the data will need some housekeeping: Like removing some rows or columns, Pivoting or Unpivoting the data, Splitting some columns, replacing values, creating some calculations, merging or appending files. All that is done by using Power Query withing Power BI (It works exactly like Power Query in Excel). Sometimes we need to connect sources together by creating relationships (Exactly as in Power Pivot in Excel). By cleaning the data, we create a normalized data set ( Data Model) that is ready for visualization.
Visualizing the data
Getting insights out of huge lists of raw numbers might be overwhelming. However, we can represent the data graphically by creating Charts, Maps, Tables, Cards, matrices and be able to get insights at a glance. In Power BI we make our reports interactive as well.
Sharing the Data and Reports
We finally need to share our reports and findings, with colleagues and clients, in a way that they can access our reports from any device and interact with our visualizations.
This is what will be doing in this project, following the same steps to get answers to many questions we might have.
Let’s understand our source data
Our source data is in 2 files an Excel file with 2 sheets, and a CSV file.
The Excel File “My Data” stores information about the number of tasks each employee has done, and the number of random samples withdrawn, how many defects or fatal errors were found and who was the auditor.
On another sheet we have the auditor ID and name
The employee’s information is stored in another csv file and cannot be analyzed as is. It needs to be cleaned first. Note the Employee ID is a matching field between this csv file and the “Quality Data” worksheet.
Step 1: Bring the data into Power BI Desktop
Power BI Desktop is available as a Free download from powerbi.microsoft.com
Open Power BI Desktop, sign-in using your credentials (you may skip this step if you are not publishing to the clouds). A yellow splash screen appears, close it by clicking in the upper right corner.
Power BI desktop has 4 views: Report / Data / Model views are arranged in a pane on the left-hand side, while Power Query opens as a separate window by clicking on Transform Data on the Home Tab.
We start by bringing the Excel File.
On the Home Tab ►Click on “Get Data” ► Select Excel Workbook ►navigate to the location of the file “My Data” ► Select it ►A Navigator window opens ► Explore the 2 tables “Auditors” & “Samples” (blue horizontal line) and the 2 sheets. The tables are well structured and can be used directly without any further work ► Check the boxes for the tables ► Click on “Load” ► the 2 tables are added and appear if the Fields pane to the right side.
Now we want to bring the CSV file “Employee Info”
On the Home Tab ► Get Data ►Text/CSV ► Navigate to the file and select it . This file needs some transformation ► Click on “Transform Data” ► to open the Query Editor.
Step 2: Cleaning the Data
The Query Editor
1- The Queries pane to the left shows the 3 sources. It can be collapsed.
2- The Query Setting to the right shows the “Name” of the selected query and the “Applied Steps”
3- Click on the deletion symbol to the left of the step “Change Type”
4- Right click the single column header “Column1” and select “Split Column” (also available on the Home tab ► By Delimiter.
5- The “Split Column by Delimiter” box opens. Accept the default and hit Ok.
6- The single column is split into 4 columns.
7- On the Home tab ► Click on “Use First Row as Headers” (watch the applied steps. Employee ID becomes right aligned and the Data Type changes to “Whole number”
8 – On the Home tab ► click on “Close and Apply” to go back to Power BI main window.
9 – Click on the “Model” view ► Relationships between the 3 sources are auto detected. If we want to make changes, we can click on “Manage Relationships“, but we do not need to make any change.
Step 3: Create Measures (Calculated Fields)
Switch to the “Data” view ► Select the “Samples” table from the Fields pane. On the “Table tools” tab ► Click on “New Measure“, we will be creating 5 measures by typing in the Formula bar. Hit Enter after each measure and keep an eye on the Fields pane.
Sample % = DIVIDE( SUM([Samples]),SUM([Total Tasks]),Blank())
Format as %
Create the next Measure (Same process)
Defect % = DIVIDE(SUM([Defects]),SUM([Samples]),Blank())
Format as %
Create the next Measure (Same process)
Fatal Error % = DIVIDE(SUM([Fatal Errors]),SUM([Samples]),Blank())
Format as %
Create the last measure:
Quality Score = IF([Defect %]=Blank(),BLANK(),1-[Defect %])
Step 4: Create the Visualization
I prepared for the visualization background in PowerPoint by creating 2 simple slides using Shapes, text boxes, and Icons. I then saved the 2 slides as pictures, and I have them ready to use in Power BI as JPEG (available in the Exercise folder).
Page Background
In Power BI, click on the “Report” view.
Under the Visualization pane ► Click on the “Format” tab (Roller icon)►expand “Page Background” ►click on “+Add Image” ►Navigate and select “Background1” image ► set the “Image Fit” to “Fit” ► set “Transparency” to 0%
Our page looks like the figure.
Let’s rename the page (as we do in Excel) “Quality Analysis”.
Creating Cards
Under Visualization pane ► Click on “Card“. Drag “Total Tasks” from the Fields Pane ► to “Fields” box in the Visualizations pane.
Click on the Format tab ► Turn Off the “Category label” and “background“. Expand “Data label” ► Change the Font color to Blue, Font Type to “Segoe UI Bold” and Font Size to 30pt.
Resize and reposition the card on top of the Total Tasks rectangle in the Background. Copy and paste the Card to the next rectangle “Samples” ► Replace the Field by dragging “Samples” on top of the existing field in the visualization pane to replace it. Decrease the Font Size to 20 pt. Make another copy and reduce the Font size to 12 pt. and replace the field with Sample % (NB make sure the formatting of the measure is %)
Repeat by copying the previous 2 cards and paste them twice, replacing the fields by Defects & Defects % then by Fatal Error & Fatal Error %. Make sure the measures are formatted as % on the Measure Tools tab.
For the Quality Score we will create a Gauge visual by clicking on “Gauge” icon. Drag the “Quality Score” field to the values (Format the measure as %).
On the Format tab, turn Off the Data label, Category and Background. Change the Data Color to Blue. Resize and reposition the gauge on the leftmost rectangle.
Creating Charts
We start by creating a Bubble chart for the Samples vs Defects. Drag the “Samples” to the X axis, drag “Defects” to the Y axis and “Emp Name” to the Details. Drag “Fatal Errors” to the Size & “Quality Score” to the Tooltip
On the Format Tab: format the X Axis in Black size 10pt. Segoe UI Bold font and gridlines blue and solid. Same for the Y Axis.
Change the chart Title to “Samples vs Defects per Employee“, Dark blue Background and white font, Segoe UI Bold.
Expand Data Color and click on fx. to apply Conditional Formatting based on the “Quality Score“. I selected Red / Yellow / Green (You can select a Hex color from the website www.color-hex.com ) ► hit Ok and turn off the Legend. Add a border (Blue and 5pt.). You can change the bubble size on the Format tab by expanding Shapes and change Size to 12pt.
Move and reposition the chart to the upper left corner.
Our first chart should look like this
Copy and Paste the previous chart just below it. Change it to an Area Chart, drag “Date” to the Axis and “Fatal Errors” to the Values. On the chart controls click on the 2 down pointing arrows to drill down to Months.
On the Format tab turn on Data Label and for the Data Color revert to default.
Change the Chart Title to “Fatal Errors by Months“.
There is an increase in Fatal Errors in March that I want to analyze, so I hover over March ►right Click and select Analyze ► Explain the Increase. A detailed report is generated analyzing the fatal Errors by Auditor, by Employee, by Supervisor…etc.
Copy and paste the previous chart. Change it to a Donut Chart. Replace current fields ►Drag “Supervisor” to the legend and “Quality Score” to the Values. On the Format tab ► expand “Detail Label” ► for the label Style select “Category, data value“.
Change the Chart Title to “Quality Score By Supervisor
Copy the Donut chart and paste it below it. Change it to a Bar Chart. Replace current fields ►Drag “Auditor Name” to the Axis and “Sample %” to the Values.
Use the Format painter to copy formatting from the Area Chart to the Bar chart.
Change the Data Color as needed to match other visualizations.
Change Title to “Sampling % By Auditor“
The last visualization will be a Map.
Click on a blank area in the canvas then click on the Map icon under Visualization. Resize and reposition the placeholder to fill the remaining part of the report. Drag “Work Location” to the location box and “Quality Score” to the Size.
On the Format tab ► Change the Bubble Size to 8.
Use the Format painter to copy the formatting from anyone of the other charts.
Rename the Map “Quality Score by Work Location“
More Report Pages
Next, we will create another page for the report specially designed for the Tooltip. Click on the plus sign to add a page ► rename it “Employee ToolTip“. Right click and Hide the page.
On the Format Tab ► expand “Page Size” ► set the Type to “ToolTip“. For the Page Information, Turn On the ToolTip
Copy the Bar Chart from the previous page and replace fields: “Emp Name” to the Axis and “Quality Score” to the Values. Change the Title to “Quality Score By Employee“.
On the Format Tab ► Y Axis ► Drag the slider of the “Maximum Size” to the far right, and our ToolTip is ready to use
Go to the “Quality Analysis” page of the report, select the Donut Chart and on the Format Tab ► expand ToolTip and under Page select “Employee ToolTip“.
Repeat for the Bar Chart. Test by hovering over anyone of the Bars so we can see in the ToolTip the Quality Score for those employees audited by this specific auditor.
Create one more page for the report, name it “Employee Performance“.
Insert a Matrix visualization and resize it to fill the whole canvas.
Drag “Supervisor” & “Emp Name” to the Rows box. Drag these 8 fields to the Values: “Total Task“, “Samples“, “Samples %“, “Defects“, “Defects %“, “Fatal Errors“, “Fatal Error %” and “Quality Score“.
On the Format tab ► Change Style to “Minimal“
For the Row Header, Column Header and Values: Change the Font size to 12 and bold the Headers (Segoe UI Bold).
For the Column Headers► use a Dark blue Background and a white Font.
For the Rows Headers turn Off the “Stepped Layout”.
Expand Subtotals ► Change the Background Color to light blue.
Use the controls of the Matrix to test and Drill down and drill up.
Publishing Your Report
Now the report is ready ► Save it, we can then publish it to Power BI Services. You should sign-in to Power BI services.
Click on the Home Tab ►Click on Publish to the far right ► a dialog box opens displaying the different workspaces available ► Select a workspace ► Click Select
NB: A Workspace is a storing location on Power BI services like folders on your local computer. You can create as many workspaces as you want, rename them, and nest them.
Publishing is initiated and takes few seconds followed by another window showing that publishing was successful.
From the same window you may click on Open to view and interact with your report in Power BI Services. You can share it from there as well.
Here is my report in Power BI services, it’s fully interactive. I can test all the functionality. Note I only see 2 pages of the report because the “Employee ToolTip” page is hidden.
You can also switch to Full Screen mode for better visibility.
If you want to make any changes ► Click on Edit and you will be able to work on the report as if you are in Power BI Desktop.
Finally, you can Share the report with your colleagues and team and set the level of permission.
Now you need to practice and recreate the report using the Exercise files provided. Feel free to modify or change the visualizations.
Your feedback about this post is much appreciated
