Data Visualisation with Power BI Desktop

Power BI Desktop

As most of you guys know Power BI Desktop is released. I should say, it’s awesome. There are heaps of changes in compare with its preview edition Power BI Designer. I’ve written a series of posts regarding creating a report and dashboard using Power BI Designer before. You can find them here. Now I want to explain the same thing in Power BI Desktop. I’ll cover lots of new features in this post and I hope you enjoy it.

Get Data

  • Open Power BI Desktop
  • Click on Get Data. You can also get data from recent data sources or even open a predefined report stored in pbix format

Power BI Desktop 01

  • We use Adventure Works DW 2012 database as sample, you can open your real world data source
  • Click on “SQL Server Database” then “Connect”
  • In this sample we are connecting to a “SQL Server Database”
  • Click “Connect”

Power BI Desktop 02

  • Enter the server and database names. The database name is optional here so you can leave it blank. However, You’ll need to select it in the next pages. So in this sample I enter the database name. You can also write your own custom SQL statement. Click OK.

Power BI Desktop 03

  • If you’re using a SQL Server user then tick “Use alternate credentials” and enter the corresponding username and password. Otherwise just click connect

Power BI Desktop 04

  • In this sample we’re going to analyse reseller’s sales. So we select the following tables from the database
        1. FactResellerSales
        2. DimCurrency
        3. DimDate
        4. DimEmployee
        5. DimGeography
        6. DimOrganization
        7. DImProduct
        8. DimProductCategory
        9. DimProductSubcategory
        10. DimPromotion
        11. DimReseller
        12. DimSalesTerritory
  • Click Load

Power BI Desktop 05

  • Now you should see selected tables on the “Fields” pane

Power BI Desktop 06

So far we got the data we need to analyse. Now we should go to the next step.

Make Names More User Friendly

This part is going to be a very easy, but, time consuming part of our work. The more objects you have the more time you need to take on this part.

Rename Queries and Fields

In Power BI Desktop you can rename the queries and their fields from 3 different places.

  1. From report view
    • Select the report view from the left panePower BI Desktop 07
    • Right click on the query or field from the “Fields” pane and select “Rename”Power BI Desktop 08
    • Rename the object
  2. From Data view
    • Click on “Data” view from the left pane
    • To rename the query you can right click on the query from “Fields” pane then “Rename”Power BI Desktop 09
    • To rename the fields you can either expand the query, right click on the field then “Rename” OR you can simply click a query from “Fields” pane then right click on the column and “Rename” itPower BI Desktop 10
    • If you are a keyboard guy like me, you can press F2 on the queries or fields from “Fields” pane to rename them as well.
  3. From Query Editor
    • It doesn’t matter in which view you are, just click “Edit Queries” from the ribbon
    • Select the query
    • For renaming the query you need to rename it on “Query Settings” pane then press EnterPower BI Desktop 11
    • To rename the fields right click on the column then renamePower BI Desktop 12
    • After renaming you’ll see that another step is applied to the query. You can see the codes behind the seen by clicking on “Advanced Editor”Power BI Desktop 13

Hide Queries and Fields

To hide a query or a field you can do the same as we did for renaming the queries or fields. You just need to select “Hide” when you right click on the object from the report view.

 Power BI Desktop 14

Or select “Hide in Report View” when you do the same in the report view.

Power BI Desktop 15

Unfortunately, we need to hide the corresponding fields or queries one-by-one as multi-select is NOT available here that makes it more time consuming.

NOTE: You can NOT hide fields from Query Editor.

UPDATE: Multi-select is available now with the new release of Power BI Desktop (Ver. 2.26.4128.403) in “Relationships” view:

  • Multi-select desired columns
  • Right click on the columns
  • Click “Hide from “Hide in Report View”
  • image

It’s much easier now to hide unneeded columns from the reports.

Managing Relationships

You can manage relationships by clicking on the “Manage Relationships” button available on the ribbon.

As you can see in the screenshot there are some inactive relationships between the tables. This is because there is another relationship between those two tables. For instance, order date and ship dates links are inactive as there is an active relationship between “Reseller Sales” and “Date” tables. The “Due Date” link between “Reseller Sales” and “Date” tables is activated by default so we are not able to activate the other relationships.

Power BI Desktop 16

But, what should we do in these cases? Previously I explained how to implement role-playing dimensions in SSAS Tabular model . The same concept applies to Power BI Desktop so I won’t explain it here again. So we need to import the date dimension two more times to our Power BI Desktop model. Then we need to create new relationships between the imported date tables and the Reseller Sales table. As I created DueDate, OrderDate and ShipDate views on SQL Server  before I just need to import them to my Power BI Desktop model. To do so you need to “Get Data” then make names more user friendly.

Click on the “Relationships” view to see how your model looks like.

Power BI Desktop 17

Note: As you can see I left the Date table in the model. I’ll express the reason in another post showing you  a very effective way to have all date measures in a single chart. So you can decide either to import all date tables into your model or implementing the report using just one date dimension depending on your client’s needs .

After importing all date tables to the model it’s time to manage the relationships.

  • Click on the “Manage Relationships” button from the ribbon
  • As you can see the new added date tables are not in the list at all

Power BI Desktop 18

  • Click New
  • Select “Order Date” table from the first dropdown list
  • Click on “OrderDateKey” column
  • Select “Reseller Sales” table from the second dropdown list
  • The “OrderDateKey” should get highlighted automatically. If it doesn’t click on it
  • Click OK

Power BI Desktop 19

  • Do the same thing for the other date tables to create the relationshipsPower BI Desktop 20

Go to “Relationships” view to see how the model changed.

Power BI Desktop 21

Data Visualisation

Now it’s time to start the exciting part of working with Power BI Desktop. Data visualisation is the very easy with Power BI Desktop when you have a well designed model. Let’s start with some scenarios and make meaningful reports.

Well, as you saw we used Adventure Works data warehouse as a sample in this post. We imported the tables related to “Reseller Sales”.  ***

Total Sales Amount

  • Go to “Report” view
  • Expand “Reseller Sales” table from “Fields” pane
  • Click on “Sales Amount”

Power BI Desktop 22

  • Power BI Desktop creates a column chart by default. Select the chart
  • From Visualisation pane click on “Card”

Power BI Desktop 23

  • Resize the card

Sales By Product Category

  • Expand “Reseller Sales” from “Fields” pane
  • Click “Sales Amount”
  • Expand “Product Category”
  • Click “Product Category” fieldPower BI Desktop 24
  • To show the labels from the “Visualisation” pane click Format –> Data Labels–> OnPower BI Desktop 25
  • If you want to change the columns colours expand “Data Colours” and you can either change the colour for all columns or changing individual columns by clicking on “Show All” and change the colour of desired columnsPower BI Desktop 26
  • To change the chart title just expand “Title” and replace the “Text”Power BI Desktop 27

Sales and Total Costs by Region (Postal Code)

  • Click somewhere on the report view out of any other charts
  • Expand “Reseller Sales”
  • Click “Sales Amount”
  • Expand “Geography”
  • Click “Postal Code”
  • From “Visualisation” click “Map” to change the chart to a Map
  • Drag and drop “Total Product Costs” from “Reseller Sales” into “Colour Saturation” areaPower BI Desktop 28
  • From “Visualisation” pane click on “Format”
  • Expand “Data Colours”
  • Click “Diverging”
  • Change the colours for Medium, Centre and MaximumPower BI Desktop 29
  • To change the chart name expand “Title” and change the “Text”Power BI Desktop 30
  • To see more details you can zoom-in by double clicking on the map
  • Power BI Desktop 31
  • If you hover over each circle it more details will be shownPower BI Desktop 32

Based on our settings this chart is telling us the sales amount and product costs in each postal code. The bigger the circle the higher sales amount. The greener circle indicates lesser product costs. So the red circle indicates the highest product costs.

Resellers with Sales Amount Greater Than $650,000 by Sales Region

  • Add a Treemap to the report by clicking on it from the “Visualisation” pane

Power BI Desktop 33

  • Expand “Reseller Sales”
  • Click “Sales Amount”
  • Expand “Sales Territory”
  • Drag and drop “Sales Territory Region” to “Group” into the “Visualisation” pane
  • Expand “Reseller”
  • Drag and drop “Reseller Name” into “Details”
  • From “Visualisation” pane –> Filters –> expand “Sales Amount” and set “Show the items when the value: is greater that or equal to 650,000”
  • Click “Apply Filter”

Power BI Desktop 34

  • To change the chart title click on “format”, expand “Title” then change the “Text”

Power BI Desktop 35

  • If you hover over the chart more details will be shown

Power BI Desktop 36

Order Quantity, Unit Price and Sales Amount by Order Year and Product Category

  • Click on “Scatter Chart” to add it to the report
  • Expand “Reseller Sales”
  • Drag and drop “Order Quantity” into X Axis area
  • Drag and drop “Unit Price” into Y Axis area
  • Expand “Product Category”
  • Drag and drop “Product Category” into Legend area
  • Expand “Order Date”
  • Drag and drop “Year” into Details area
  • Drag and drop “Sales Amount” from “Reseller Sales” into Size area

Power BI Desktop 37

If you hover over the chart you can see some more details.

Power BI Desktop 38

Adding Some Slicers

  • Add a “Slicer” to the report by clicking on “Slicer” from the “Visualisation” pane
  • Expand “Geography”
  • Click “Country”
  • Add another slicer for “State Province Name” as well

Power BI Desktop 39

We will use these slicers to filter the whole report. So if we need to see the report just for analysing the data for the Unites States and Canada we just need to simply click on the on the slicer.

Power BI Desktop 40

Interactive Filtering Ability

Power BI Desktop is a really amazing data visualisation tool with tons of features you can use. One of the great features od Power BI Desktop is “Interactive Filtering”. Interactive filtering basically is the ability of selecting values directly on a chart and having that filter on other data regions. It’s awesome isn’t it? So besides the slicers or the page filtering you can setup, you can easily click on a chart and see the effects all over the report. For instance, if you click on “Components” column from “Sales by Product Category” column chart you’ll that all other charts will be filtered very nicely.

Power BI Desktop 41

Another example is when you want to see how your bike sales was in 2007. You can simply click on the desired circle on the scatter chart.

Power BI Desktop 42

Publishing the Reports to Power BI Website

One of the fantastic new features added to the Power BI Desktop is the Publish button on the ribbon. Now we can simply publish our reports to the cloud directly from Power BI Desktop.

  • Click on “Publish” button on the ribbon
  • You just need to type your Power BI credentials and you’re good to go

image

Power BI Desktop 43

Power BI Desktop 44

You can simply click on the Open in Power BI link to open your Power BI report on the cloud.

Power BI Desktop 45

There is a lot more to say about the awesomeness of Power BI Desktop. I will write some new posts regarding the other features soon.

4 thoughts on “Data Visualisation with Power BI Desktop”

    1. Hi Jack.

      Welcome to biinsight.
      Date dimension is a role playing dimension.
      Power BI, as well as Power Pivot and SSAS Tabular models, cannot handle multiple relationships between two tables to drive different roles.
      So you can create database views for each role in SQL Server side (for instance Due Date, Ship Date and Order Date) then import the views into the model.
      OR you can import DimDate multiple times into the model then create the relationships.
      I already put the link in the article.
      For more information please refer to http://biinsight.com/role-playing-dimension-ssas-tabular/.

      Cheers

      1. Another option is to import the Date table (Order Date) once into Power BI. Then create two new queries (Ship Date, Due Date) that reference the first. This approach minimizes the data load from the back end. Not a big deal for a date table but could be important for large role playing dimensions.

        1. Hi Kris.
          Welcome to biinsight.
          Correct, it would be very useful especially when you have several role playing dimensions with several roles. Power BI model has 250 MB storage limitation which could be a serious restriction indeed. When you’re working on a big data source this approach can save some storage.
          Cheers

Leave a Reply