An Introduction to Power BI part 2: Building a Dashboard

Introduction

In this section we will take a look at the various elements of the Power BI canvas:

Three views available in Power BI Desktop

The three views, highlighted below, which can be found on the left hand side of the screen are the Report view, Data view and Model view

• Report view - used to create reports and visuals, where most of your creation time is spent 
• Data view - this view contains tables, measures and other data used in the data model associated with your report.
  • It is also used to transform the data for best use in the report's model. 
• Model view - used to manage the relationships among tables in your model                                                                                                 

Building the reports – right hand side of the screen

On the right side of the screen, you will find three sections for Filter, Visuals and Data with their use case described below:

• Data 
  • Contains all your data tables with fields, added columns and measures 

• Visuals 
  • Visuals available 
    • Drag onto the canvas 
    • Many more visuals available from the Microsoft store 
  • Visuals and formatting Icons 
    • Build visuals - add in fields to include in the visual 
    • Format the visuals 
      
      
• Filters 
  • Filter for this visual, this page or all pages 
  • Either generated automatically or manually
    

And now … over to Power BI itself!

Loading data into Power BI

Following the instructions below, the finished product of the dashboard you are about to build should look like the screenshot below; 

The Data

The version of the Power BI used to build the above visualization is the February 2023 version. 

Revenues Table

We have created some fictitious data for revenues and expenses for a fictitious asset management company has been created and used in the model .

This was uploaded from a simple Excel spreadsheet

 There are various attributes – for filtering and analyzing:

• Revenue and expense type
• Time series data including Year and YY MM
  • Data runs for three years – 2020, 2021, 2022
• Location
• Share class
• Salesperson 
• Amount and amount budget 
  

The data is saved in a file called Power BI presentation data - March 2023.xlsb

Note that an Excel binary file (.xlsb) uses memory more efficiently than a normal Excel file (.xlsx)

Opening Power BI and Loading the Data

• Power BI will evaluate the data and once done the Load button will appear

• Power BI will load the data to the model
  

9.  Select the Model view icon to see the data table

10.  Select the Data view icon

• Select the Revenues table on the right to see all of the data in the table
  
  

11.  Save the model File | Save and name & save to your preferred location as CIMA presentation March 2023 - building the model 

• You will see this file in your folder as a .pbix file

12. Select the Report view icon to see the reporting canvas and your table fields

Shaping the data – adding new columns

We have loaded our data into our Power BI model! Now let us look at how we can shape and clean the data.

In this section, we will add in new columns for Revenues, Expenses and Profit. 

To add in a new column we use Power BI’s programming language - DAX

1.  Select the Data view icon

• Select the Revenues table on the right 
  

2.  Select Table Tools | New Column

3.  When you click on New column, you get the DAX prompt

4.  What is DAX (Data Analysis Expressions)

• DAX - collection of functions, operators, and constants
  • can be used in a formula, or expression
  • calculate and return one or more values
• DAX helps you create new information from data already in your model.
  

5.  Using DAX

• Amount Revenues = IF([Account Class] = "Revenues", [Amount], 0)
• A DAX function consists of the following parts
  
  • The column name
  • The equals sign operator (=), which indicates the beginning of the formula.
  • When calculated, it will return a result
  • A DAX function e.g. IF
  • Parenthesis (), which surround an expression that contains one or more arguments.
  • The referenced column e.g. [Account Class], [Amount]

6.  Writing our first DAX calculation – for Amount Revenues

• Type out the DAX expression as follows:
  • Amount Revenues = IF([Account Class] = "Revenues", [Amount], 0)
    
    

• Click on the tick box to the left of the column to return
  

• Select Column Tools from the ribbon
  
• Format: Decimal number
  
• Select: comma [,]
• Decimal places: 0
  

8.  The new column will be added to the available list of fields in the Revenues table

• Select the Revenues table on the right
  
• Select Table Tools | New Column
  
• Add in the following DAX code 
• Amount Expenses = IF([Account Class] = "Expenses", [Amount], 0)
  
• Click on the tick box to the left of the measure to return 

• Select Column Tools from the ribbon
  
• Format: Decimal number
  
• Select: comma [,]
• Decimal places: 0

11.  The new column will be added to the available list of fields in the Revenues table

12.  Writing a DAX calculation – for Amount Profit

• Select the Revenues table on the right
  
• Select Table Tools | New Column
  
• Add in the following DAX code
• Amount Profit = [Amount Revenues] - [Amount Expenses]
• Click on the tick box to the left of the measure to return

• Select Column Tools from the ribbon
• Format: Decimal number
  
• Select: comma [,]
• Decimal places: 0
  

14.  The new column will be added to the available list of fields in the Revenues table

Shaping the data – using Power Query

We have added two new columns into our Power BI model.

You can also shape the data using Power Query. 

Power Query is located on the ribbon – hidden under Transform data | Transform data. 

Microsoft: Power Query is :-

• data connectivity and data preparation technology
• enables end users to seamlessly import and reshape data from within a wide range of Microsoft products, including Excel and Power BI
  
  

Let us look at a quick example of shaping data with Power Query: 

1.  Select the Data view icon

2.  Select the Revenues table on the right

3.  From the ribbon select Transform data | Transform data

4.  Select the column YY MM

• At the moment it is showing as a date field and is not processing the Years and Months properly
  

5.  From the top right of the ribbon select Text

6.  In the Change Column Type box select Replace Current

7.  The YY MM field will now be a Text field

8.  The Applied Steps recorder will be updated for this change

• File | Close and Apply | Close and Apply

10.  The changes to the YYM field have been saved – it is now a Text field

Adding our first visual – a simple bar chart

1.  Select the Report view icon

2.  Double click on the icon for a Stacked column chart

3.  It should appear on the reporting canvas

• Select the Build Visual icon (as highlighted to the right above)
• In the Build visual section, add in the fields as follows:

X-axis - Revenues [YY MM]

Y-axis - Revenues [Amount Revenues]

5.  Double check that the visual is sorted correctly

• Select the three dots […] above the upper right corner of the visual
  
  
  
  
• Select Sort axis
  
• Select Year Month
• Select Sort ascending
  

6.  The visual will look like this:

You have now created your first visual!

7.  Formatting the visual

• We will now look at the ways in which you can format the visual

8.  You can select a colour scheme

• Select View | Themes drop down arrow and select a colour scheme

• We will stay with the current colour scheme

9.  Let us look at the formatting options available

• From the Visual section:
• Gridlines | Horizontal – On
• Zoom slider – Off
• Columns | Colours – Blue
• Data Labels –  Off
• Plot Area Background – Off

• From the General section:
• Properties | Size and Position – as required
• Title – On
• Title | Text – Revenues by Year and Month
• Title | Font – Sergoe UI Bold 14
• Title | Background | Color – Light blue

• Effects | Background – On
• Effects | Background – Light blue
• Effects | Background | Transparency – 0%
• Effects | Visual border – On
• Effects | Visual border | Colour – Light blue
• Effects | Visual border | Rounded Corners – 16%

10.  You have now created your first visual!

Adding a time slicer on year

We will now look at how we can go about creating a slicer for the Year field which will enable to select a particular year on which to focus

 What is a slicer?

 Microsoft say:

• Slicers are another way of filtering.
• Displayed on the report page,
• Narrow the portion of the dataset that's shown in the other report visualizations

 When to use a a slicer?

 Microsoft says that slicers are a great choice when you want to:

• Display commonly used or important filters on the report canvas for easier access.
• Make it easier to see the current filtered state without having to open a drop-down list.
• Filter by columns that are unneeded and hidden in the data tables.
• Create more focused reports by putting slicers next to important visuals

Building a slicer

Let us now set up a slicer

1.  To keep all of the formatting and colour schemes, we will copy / paste the bar chart and convert to a slicer

2.  Select the bar chart and copy / paste

• Move the copied visual to the top of the canvas and make smaller

3.  Select the bar chart and then select slicer from the choice of visuals

4.  In the Build visual section, add in the field as follows:

• X-axis – Dates [Year]
  
  
  
• Your slicer should look like this:
  
  

5.  Let us look at the formatting options available for the slicer

• From the Visual section:
• Slicer settings | Options | Style – Tile
  
  
  
  
  
• Slicer settings | Selection | Single select – Off
• Slicer settings | Selection | Multi select with CTRL – Off
• Slicer settings | Selection | Show “select all” option” – On
  
  
  
  
  
  
  
• Slicer header – Off
• Values | Values | Font – Sergoe UI Bold 14
  
  
  
  
  
• Values | Values | Font – Sergoe UI Bold 14
• Values | Values | Border – Leave as is
• Values | Values | Background – Light blue
  
  
  
  
• From the General section:
• Title – On
• Title | Text – Select year(s)
• Title | Font – Sergoe UI Bold 14
• Title | Background | Color – Light blue
  
  
  
  
  

6.  The slicer will now look like this:

7.  Try selecting different years on the slicer and see the graph change

Adding a table to the canvas

We will now look at how we can go about creating a table looking at revenues and costs by share class

What is a table?

Microsoft say:

• A table is a grid that contains related data in a logical series of rows and columns
• It may also contain headers and a row for totals

Building a table

Let us now set up a table

1.  To keep all of the formatting and colour schemes, we will copy / paste the bar chart and convert to a table

2.  Select the bar chart and copy / paste

• Move the copied visual below the bar chart

3.  Select the bar chart and then select table from the choice of visuals

4.  In the Build visual section, add in the field as follows:

• Columns – Revenues [Share Class]
• Columns – Revenues [Amount Revenues]
• Columns – Revenues [Amount Expenses]
• Columns – Revenues [Amount Profit]
  
  
  

• Your table should look like this:
  
  
  
  

5.  Let us format the numbers so that they look tidier

• From the Visual section:
• Style presets – Bold header flashy rows

• Values | Font – Sergoe UI Semibold 13
• Values | Background | Color – Light blue
• Values | Alternative Background | Color – Slightly darker blue
  
  
  
  
• Column Headers | Text | Font –Sergoe UI Bold 13
• Column Headers | Text | Text color –Black
• Column Headers | Text | Background color –Slightly darker blue
• Column Headers | Text | Header alignment – Right 
  
  
  
  
• Totals | Values | Font –Sergoe UI Bold 13
• Totals | Values | Text color –Black
• Totals | Values | Background color –Slightly darker blue
  
  
  
  
  From the General section:
• Title – On
• Title | Text – Revenues and Profit by Share Class
• Title | Font – Sergoe UI Bold 14
• Title | Background | Color – Light blue
  
  

6.  The table should now look like this:

However, there is one thing in the table that doesn’t look quite right – some of the column titles

We would rather see Amount Revenues in the column title instead of Sum of Amount of Revenues

To get round this we can replace the summed up column calculations with summarized calculations called Measures

Shaping the data - introducing measures

Measures in Power BI are a summarization of any data

Power BI measures gives us aggregate values from multiple rows from a table.

We will create three summarized measures:

• Total Revenues
• Total Expenses
• Total Profits 

which we will insert into the table

 By doing this, we will lose the “Sum of …” in the column titles

Creating the measure Total Revenues

1.  In the Data section, right-click on table name Revenues

2.  Select New measure

3.  Type out the DAX expression as follows:

• Amount Revenues = IF([Account Class] = "Revenues", [Amount], 0)
  
• Click on the tick box to the left of the measure to return

4.  Format the measure:

• • Select Measure Tools from the ribbon
    
• Format: Decimal number
  
• Select: comma [,]
• Decimal places: 0

5.  The new measure will appear in the Data section, with an icon denoting it is a measure

Repeat for the new measures Total Expenses and Total Profit

1.  Type in and format the measures one by one as below at the DAX prompts:

• Total Expenses = SUM(Revenues[Amount Expenses])
• Total Profit = [Total Revenues] - [Total Expenses]

2.  You should see the new measures as follows:

 

Replace the columns with the three new measures

1.  In the Build visual section, remove the following fields as follows:

• Columns – Revenues [Amount Revenues]
• Columns – Revenues [Amount Expenses]
• Columns – Revenues [Amount Profit]
  

2.  Replace them with:

• Columns – Revenues [Total Revenues]
• Columns – Revenues [Total Expenses]
• Columns – Revenues [Total Profit]
  
  
  

3.  The visual should now look like this:

Add in a measure to calculate profit margin

We will now add in a measure to calculate the profit margin

1.  In the Data section, right-click on table name Revenues

2.  Select New measure

 

3.  Type out the DAX expression as follows:

• Total Profit Margin = DIVIDE( [Total Profit], [Total Revenues])
  
• Click on the tick box to the left of the measure to return

4.  Format the measure:

• • Select Measure Tools from the ribbon
    
• Format: Decimal number
  
• Select: %
• Decimal places: 2

DIVIDE function: Performs division and returns alternate result or blank on division by 0
DIVIDE [Numerator], [Denominator], [Optional alternative result)

5.  In the Build visual section, add in the field as follows:

• Columns – Revenues [Total Profit Margin]

6.  The table will now look like this:

Adding a location slicer on City

Here, we will look at adding a second slicer - this time for the City

1.  To keep all of the formatting and colour schemes, we will copy / paste the year slicer over

2.  Select the year slicer and copy / paste

• Move the copied visual to the top left of the canvas
  

X-axis – Dates [Year Month]

4.  Let us look at the formatting options available for the slicer 

• From the General section:
• Title – On
• Title | Text – Select location(s)
• Title | Font – Sergoe UI Bold 14
• Title | Background | Color – Light blue
  
  

Taking a look at Power BI Service (powerbi.com)

Loading the dashboard into the Power Service

1.  Save the model

2.  Click on the Publish icon to publish the model to the Power BI service (Power BI.com)

5.  When loading you will see this message box

7.  The report in Power BI service (PowerBI.com) will open for you

Taking a look round Power BI service

My workspace

1.  We have saved the report into the user’s own workspace, which is always called My Workspace

• My workspace stores all of the content that you own and create.
  
  
• Think of it as your personal sandbox or work area for your own content.
  
  

2.  Select My Workspace from the menu on the left

• If you have the right permissions, you can refresh the data
  
  
• Select the Dataset for your report
  
  
• Select the Refresh icon
  

•  You can even set up automatic refreshes on a timer
  

4.  Double-click on the report ‘CIMA presentation March 2023’ – building the mode to load it back up

   Note that some of these features require a Power BI Pro licence

7.  There are a wealth of materials for learning Power BI

5.  If you don’t have the Add-in, select Get Add-ins in option 3

     In option 4, search for Power BI and select the second option Microsoft Power BI

6.  You will get this appearing in your blank slide

7.  Paste the URL of the Power BI service into the Paste box as below

     Click the Insert button

Power BI Journey – Next steps