I have the data exported as Excel files. You can download the practice along files at https://drive.google.com/file/d/1FCEkyJvW4viszv9wo43QaljXI2ADpCDD/view?usp=sharing
Making sure the Excel files are closed, we are going to use Power BI’s Get Data to bring in the data. If you have the startup screen then you can locate the Get Data on the top left.
Bring in the data
I prefer you make a habit of choosing Edit (when you work a lot more, especially with large live databases, you’ll experience the why).
Check if you need to make any changes, like delete an unneeded column or filter out rows you don’t need. Then click Close and Apply on the top left corner of the Query Editor.
And you are back in the Data Model (or main Power BI window). Now load in the other data files. Below is the step for the Sales Data.
This is a case where you’ll need to get rid of unwanted columns and it’s better to do it in the Edit step and also set the Time field to Time data type. It is also good to let you know that you can actually do all this from the main Power BI window, in the Data section.
You will notice an audit trail of your steps on the right.
Don’t forget to do the Close and Apply.
After bringing in the data, you need to create relationships between the tables. That way you can create interactive reports with charts that responds to clicks on other charts. You wouldn’t also have to do a VLOOKUP (Excel formula for relating different tables) in order to use fields from different tables in the same chart.
Power BI is quite smart and tries to infer relationships between the tables you have based on the field names and the data types. Unfortunately, it always does single direction relationships and either create too many or too few relationships. So you have to get familiar with creating relationships from scratch and editing the ones it created for you.
Double click on the line of the relationship it created for you (between Branch Data and Sales Data) and set it to Both direction. Single direction means you can go in one direction only: great if you have a database background and will always remember the direction, additionally it helps prevent ambiguity in the relationships (the main advantage that should make you consider it and the trouble of always remembering to go from the dimensioning table to the data table). Alternatively, you can click on Manage Relationships to edit and create new relationships.
And below is what it would all look like when you are done.
Now it’s time to do some formula-ing. Or DAX-ing as Power BI uses Data Analysis Expressions (DAX). You can download the official Microsoft reference material for the DAX formulas here: http://download.microsoft.com/download/0/f/b/0fbfaa46-2bfd-478f-8e56-7bf3c672df9d/data%20analysis%20expressions%20-%20dax%20-%20reference.pdf
So head to the Data part (on the left) of the main Power BI window. And select the Sales Data table as we need to do some tasks on it.
So we first create a new column, by rightclicking any field in the table and selecting New Column or clicking on New Column at the extreme right under the Home menu. Then change the name from the default “Column” to any name you find more appropriate.
Sales Amount = 'Sales Data'[Price]*'Sales Data'[Quantity]
One more cool thing. Change the Sales field and other money fields to Currency. I’ll select my country currency: Naira.
Now we go into the visual part of the report creation.
Click on Reports on the left part of the main Power BI window. And let’s start with a PivotTable-like report showing products against sales amount.
In Power BI, everything is a visual. Even a table is a visual. You have to pick the visual you want and drop the fields you need into the appropriate sections of the visual components.
Notice the similarity between the Matrix visual in Power BI and the PivotTable in Excel. If you ask me, I’ll say they are exactly same. So huge bonus for you if you already use PivotTable in Excel.
See how everything is a visual.
Now we add a Gauge that shows sales against target.
And you can change the type of calculations done on any field you use. Just click on the dropdown arrow beside the field name in the visual panel.
And now we add bar chart of sales by products against targets.
And finally, we add a Pie Chart.
See below what I did to the Slicer to improve its look.
You can also add images, shapes and text boxes to your report.
And we are done with our first report building from scratch.
Next phase is to publish it and share with our boss and colleagues.
If you’ve not signed in to your Power BI account, you will be prompted to.
Then you pick the workspace you want the report published to. In every Power BI account, there is a default workspace called My workspace. Workspaces are what you create when you want to collaborate with colleagues on a report. It allows for equal access to making changes to the report and those changes are propagated rather than limited to your own copy of the report. You create them from the online Power BI service (www.powerbi.com).
Then click on the report link upon successful publishing.
And here is what the online published version is like.
And you can do super cool stuffs like export the entire report as a PowerPoint deck or print or even publish as a web link that can be shared with anyone. Just click on File.
You can also edit the visuals or add new ones, even on new report pages. Just click on Edit and you get access to the type of designer tools you already are familiar with in the Power BI software.
Just so you know, you can do the pinning that creates the dashboard from either the report view mode or the report edit mode.
To create the dashboard, lets pin the entire report. That is called Pin Live Page and you can see it at the upper right side above the report itself.
I pick New Dashboard and give it a name.
And I choose an Existing Dashboard, picking the one I already created.
I expand Workspaces on the mid left and scroll to the dashboard I created.
And here it is.
See that it has all the things I pinned. You can move the visuals around to re-arrange them and can also adjust the size.
You can see in the screenshot below, the interesting features I hinted that it has.
You can also subscribe to receive daily emails when the dashboard is refreshed (usually means updated with new data).
In the page that will come up, you will see all the settings that control how you consume the reports from automatic scheduling of refresh to update the reports with new data without your manual intervention to sending you an email notification when a the report has been refreshed (data updated).
The first settings tab is General. Allows you to set privacy – whether to allow Microsoft get performance log from you or not. And Language, account closure, enable developer mode for coding custom visuals and enable ArcGIS Maps for Power BI.
It is good to make sure that you are configuring the settings for the right dashboard.
The third settings tab is Datasets. It allows you to schedule refresh which depends on you manually setting gateway connection if your datasets are not on a cloud or publicly accessible datasource/database. You will notice a yellow warning notice saying that you’ve not configured a data gateway for the data sources you used (this is because the Excel files we used as the data source are locally saved on our PC and not from a cloud or internet data source).
Once you configure a data gateway on your PC and register the Excel files as data sources housed in that gateway, the warning will go off and you can set the scheduled refresh.
You can allow Cortana to access your datasets relying on the Q&A feature of Power BI. Also, you can preset some Q&A questions.
The fifth settings tab is Alerts. It allows you manage the triggers you’ve set regarding some metrics/values reaching a threshold figure.
The last settings tab is Subscriptions. It allows you manage the email subscriptions you have to existing dashboards so that you get a daily notification once the dataset (ultimately, the report and dashboard) is refreshed (usually indicating new data added).
And so we have come to the end of our sample sales analysis report in Power BI.
Again, you can download the practice along files at https://drive.google.com/file/d/1FCEkyJvW4viszv9wo43QaljXI2ADpCDD/view?usp=sharing
0 comments:
Post a Comment
You can be sure of a response, a very relevant one too!
Click on Subscribe by Email just down below the comment box so you'll be notified of my response.
Thanks!