Business Data Analysis With Excel 2007 / 2010 Power BI Boot Camp Learner Guide

Power-BI-Boot-Camp-Learner-Guide

User Manual:

Open the PDF directly: View PDF .
Page Count: 95

Download
Open PDF In Browser	View PDF

Power BI Boot
Camp

Power BI College

www.powerbicollege.co.za
info@powerbicollege.co.za

Power BI Boot Camp

Table of Contents
Introduction - Power BI ....................................................................................................... 5
Signing Up for PowerBI ...................................................................................................... 6
Using PowerBI..................................................................................................................................... 6
Downloads ........................................................................................................................................... 7
Creating a Report in PowerBI ............................................................................................................. 7

Power BI Interface ............................................................................................................... 8
Top of the Menu Screen ...................................................................................................................... 9

Creating Reports in Power BI ........................................................................................... 10
Two Versions of Power BI ................................................................................................................. 11

Overview and Interface Power BI Desktop ...................................................................... 12
Overview and Interface ..................................................................................................................... 12
Accessing Data ................................................................................................................................. 13
Power BI Workspaces ....................................................................................................................... 14
Formatting Data in Power BI Desktop ............................................................................................... 14

Creating Reports in Power BI ........................................................................................... 16
The Power BI Interface ..................................................................................................................... 16
Creating a Table ................................................................................................................................ 17
Formatting the Table ......................................................................................................................... 18
Sorting Tables ................................................................................................................................... 19
Creating Multiple Measures .............................................................................................................. 19
Formatting Options and Table Styles ................................................................................................ 21
Conditional Formatting ...................................................................................................................... 21

Different Methods of Aggregation ................................................................................... 23
Tables and Cards .............................................................................................................. 27
Creating a Matrix Table ..................................................................................................................... 27
Creating a Multi Row Card ................................................................................................................ 28
Creating a Card ................................................................................................................................. 28
Quick Calc Option ............................................................................................................................. 30

Filtering Data ..................................................................................................................... 31
Slicers ................................................................................................................................................ 31
Filters ................................................................................................................................................. 32
Visual Filters ...................................................................................................................................... 32
Page Filters and Report Filters ......................................................................................................... 34

Visualizations .................................................................................................................... 36

2|Page

Power BI Boot Camp

Column Graphs ................................................................................................................................. 36
Stacked Bar Graphs .......................................................................................................................... 39
100% Stacked Column Graph ........................................................................................................... 40
Cross Filtering Between a Column Graph and Bar Graph ................................................................ 41
Filtering and Highlighting ................................................................................................................... 42

Graph Options ................................................................................................................... 44
Using Color Gradients ....................................................................................................................... 44
Accessing the Data behind a Graph ................................................................................................. 46
Export Data ....................................................................................................................................... 47
Sorting Options.................................................................................................................................. 47

Trend Analysis – Time Intelligence.................................................................................. 49
Trend Lines ....................................................................................................................................... 50
Forecasting ........................................................................................................................................ 51
Area Graphs ...................................................................................................................................... 53

Additional Graph Types .................................................................................................... 54
Combination Graph ........................................................................................................................... 54
Pie and Donut Graphs ....................................................................................................................... 55
Treemap Graph ................................................................................................................................. 55
Geographical Analysis - Mapping ..................................................................................................... 56
ScatterPlots and BubblePlots............................................................................................................ 58

Creating an Interactive Dashboard .................................................................................. 61
Publishing the Dashboard ................................................................................................................. 62
Pinning Visuals to a Dashboard ........................................................................................................ 63
Q & A Feature ................................................................................................................................... 64
Inviting Other Users........................................................................................................................... 65
Accessing a Shared Dashboard ........................................................................................................ 66
Power BI on Mobile ........................................................................................................................... 67

Assignment ....................................................................................................................... 68
Enhancing Power BI - DAX Formulas .............................................................................. 69
DAX Formula Language .................................................................................................................... 69
Simple Calculations in DAX .............................................................................................................. 70
Creating Time Intelligence Functions ................................................................................................ 71
Creating a Date Table ....................................................................................................................... 75

DAX Measures ................................................................................................................... 77
The Calculate Formula ...................................................................................................................... 79

Relationships .................................................................................................................... 81

3|Page

Power BI Boot Camp

Employee Master Challenge ............................................................................................................. 83
Creating Custom Visualizations ........................................................................................................ 84
Using the Related Function ............................................................................................................... 86

Enhancing Your Power BI Data ........................................................................................ 87
Transforming Data............................................................................................................................. 87
Aggregating Data into New Tables ................................................................................................... 88
Working with Queries and Applied Steps .......................................................................................... 90

Analysis with Excel ........................................................................................................... 92
Creating Measures in the Data Model............................................................................................... 94

4|Page

Power BI Boot Camp

Introduction - Power BI
Power BI is a new cloud based Business Intelligence technology from Microsoft.
The cloud based Power BI system provides an easy to use system for users to
create online reports, analyse and visualize data, and create interactive dashboards.
Power BI also provides a Desktop Designer that is more powerful than the online
cloud based solution. The files from the Desktop Designer can be easily uploaded to
the cloud. The Desktop Designer also provides access to powerful tools that make it
easy to import, transform and model data.
The Key Benefits of Power BI


Cloud based infrastructure able to access reports and dashboards through
laptops, tablets and smart phones



Quick development process. New features are being released in PowerBI
every month. The development process is not linked to the long development
processes of Excel



Microsoft is releasing new connections to a wide range of different data
sources every month e.g. Google analytics, Facebook, SalesForce, Quicken
and Acumatica



Provides open source visualizations. Current visualizations include the
standard line, column, bar, pie and area, but also provides treemaps,
scatterplots and combination graphs. The Power BI Gallery makes it easy to
also import and use a wide range of new visualizations



Powerful filtering and highlighting capabilities. PowerBI makes it easy to use
cross filtering and highlighting to get new insight and intelligence from your
data



Integration and possible integration into a wide range of Azure services such
as Stream Analytics and AzureML (Azure Machine Learning)

5|Page

Power BI Boot Camp

Signing Up for PowerBI
Power BI provides a free sign up and usage within certain parameters. Power BI
also provides a professional service which provides access to more sophisticated
features especially in terms of refreshing data and storage. The professional version
has a monthly fee.


Go to www.powerbi.com



Enter the email address that you want to use for the system

PowerBI will take you through the registration and sign up process. Please follow
the process to receive a login and password.

Using PowerBI
After you login into Power BI you are provided with the following options:



Datasets – Load your data



Reports – Create reports and visualizations



Dashboards – Create dashboards from reports and visualizations in Reports

6|Page

Power BI Boot Camp

Downloads
The downloads option provides access to commonly downloaded files. You are able
to download the following files:


PowerBI for Mobile



PowerBI Desktop



Analysis Services Connector



PowerBI Personal Gateway

Creating a Report in PowerBI
1. Select the + next to Data Sets
2. Select Files

3. Select Local Files
4. Load the TrainingData1.xlsx file

7|Page

Power BI Boot Camp

Power BI Interface
The data is loaded into the Power BI interface. The interface consists of three main
areas:

The left hand side provides a menu that allows easy access to your data sets,
reports and dashboards. There is an option to hide the navigation pane which will
provide more space when you are working. Next to the Power BI word is an icon
that allows you to access Microsoft online software.

8|Page

Power BI Boot Camp

Top of the Menu Screen
On the top right hand side is menu that provides access to the following features:


Display in full screen mode. This is particularly useful when you are
reviewing reports and Dashboards



Settings – Create, manage and view content packs. Also manage different
gateway settings for data access. The default settings for your Power BI
environment and different development tools can be accessed from this menu
option.



Downloads for Power BI Desktop, Mobile and Gateway software



Help – Access different Help resources

The last two options allow the user to provide feedback to Microsoft and to Sign Out.

9|Page

Power BI Boot Camp

Creating Reports in Power BI
The Training Data has been loaded into the Power BI model. This allows the user to
easily create reports including tables and various forms of visualizations.
1. Select the ellipses next to the TrainingData1 data source

Options include the following:


Rename – Rename the Data Set name



Remove – Remove the Data Set from Power BI



Schedule Refresh – Schedule the refresh of the data source



Refresh Now – Refresh the Data Source now



Analyze in Excel – Users are able to analyse and use data from Power BI
data sources in Excel.



Quick Insights – Power BI will analyze your data and will present a list of
insights that it determines from the data



Security – Setup security against the data source. Security will ensure what
records and data that users are able to view.

Select the Quick Insights option. Power BI reviews the data and displays a list of
insights from patterns that are found in the data. The Insights are displayed
Microsoft Power Point slide with a visualization and an explanation of the insight.

10 | P a g e

Power BI Boot Camp

Two Versions of Power BI
Power BI has two versions. The cloud version and a desktop version. Each version
is able to import data and create interactive reports. However, there are some
important differences between the two versions:


The cloud version is able to create dashboards, use Q & A and controls the
sharing of dashboards to other users



The Power BI Desktop version allows the creation of custom calculated
columns, measures and data models

The process to create reports in both versions is exactly the same. So in this
training we are going to use the Power BI Desktop to create the reports.

11 | P a g e

Power BI Boot Camp

Overview and Interface Power BI Desktop
Overview and Interface
1. Open the Power BI Desktop
The Power BI Desktop interface opens.

The Power BI splash screen provides access to a number of useful features:


Access the Power BI forums – The forums are a great place to ask
questions, get support and to collaborate with fellow Power BI users.



Power BI Blog – The Power BI blog provides news and information on the
latest updates and features that have been added to Power BI



Tutorials – Complete easy to follow tutorials



Get Data – Load or access data sources that you are going to use with Power
BI



Recent Sources – Display a list of recently saved Power BI reports



Open Other Reports – Access other Power BI reports that have been saved

12 | P a g e

Power BI Boot Camp

Accessing Data
Power BI allows data to be loaded from many different sources such as Excel, csv,
SQL databases, Oracle and many others.

Different data sources include:


Excel



CSV and Text



Databases such as SQL Server; Oracle; IBM DB2



Azure data sources



Other data sources such as online SAAS platforms

1. Select Get Data
2. Select Excel – Click on Connect
3. Select TrainingData1.xlsx
4. Select the Data1 Table
5. Select Load

13 | P a g e

Power BI Boot Camp

Power BI Workspaces
The Power BI interface includes three different interfaces that the user is able to
work with.



Reports – Create reports, data analysis and dashboards



Data – Review and model your data



Relationships – Review and work with your relationships

Formatting Data in Power BI Desktop
The Power BI Desktop interface screen is opened. To start with we are going to
learn to format our data and to create a couple of new fields.
1. Select the Data option
2. Select the Modelling menu option

14 | P a g e

Power BI Boot Camp

Change the formatting of Fields
Power BI uses the formatting that has been setup in data model in reports and
visualizations.
1. Click on the Unit Cost field
2. Select Decimal number and 2 decimal places
3. Repeat for Price
4. Sales, Cost of Sales and Profit – Select thousands separator and no
decimal places
Add Two New Fields
Later in the course we are going to learn about the DAX formula language for
creating custom calculations. For now, we are going to add two simple fields to the
data model to display the Year and Month Number of the Order Date.
1. Select New Column
2. Add the following columns
a. Year = Year([Order Date])
b. Month = Month([Order Date])
Two new fields for Year and Month are created in the data model.

15 | P a g e

Power BI Boot Camp

Creating Reports in Power BI
In this section we are going to review how to create reports, tables and visualizations
in Power BI. The process for creating reports is the same in the Cloud version and
in the Desktop.

The Power BI Interface
The menu bar in Power BI looks very similar to the Excel interface. At the top there
is a ribbon which provides easy access to common functions.



Clipboard – Cut or Copy any items within the Power BI canvas. The Format
Painter can also be used to copy the formatting of an item



External Data – Access new data sources or recently accessed data sources;
enter new tables of data; edit and transform the current table of data or
refresh with any new data from the data source



Insert – Insert visualizations; pages; text boxes; images and shapes



Visuals – Edit methods of interaction between visualizations; arrange,
distribute and align the items



View – Change the Page View options



Relationships – Create and manage relationships between tables of data in
Power BI



Calculations – Create new Measures using the DAX formula language in
Power BI



Share – Publish your reports and dashboards to the cloud Power BI interface

16 | P a g e

Power BI Boot Camp

Creating a Table
The Report editor consists of a canvas area to the left of the screen. On the right
hand side is a list of the fields that are available in the data source. In the middle
section is a list of Tables and Visualizations that are available to the user.

1. Drag the Manufacturer field to the Canvas
2. Drag the Sales field on top of the Manufacturer field
Power BI will create a Table displaying the Total Sales by Manufacturer.

17 | P a g e

Power BI Boot Camp

Formatting the Table
In the Fields section of Power BI; the list of fields in the Table are displayed.

1. Select the Format option
The Format option displays a number of configuration options:


General – Change General settings of the Table
o Totals – Turn Totals on and off
o Auto Size Column Widths– Automatically change the size Table
columns
o Text Size – Change the Text Size of the Table
o Positioning – Change the positioning of the Table



Title – Add a Title to the Table
o Change the Text, Font Colour, Size, Alignment for the Title



Background – Change the Colour and Transparency of the background



Lock Aspect – Lock the aspect ratio of the Table, when the Table size is
being changed.



Border – Apply a border and border colour to the Table

18 | P a g e

Power BI Boot Camp

Sorting Tables
To Sort the Table click on the Header of the field in the Table. Numeric fields will
sort from highest to lowest and lowest to highest; Text fields will sort from A to Z and
Z to A and Date fields will sort from Oldest to Newest and Newest to Oldest.
1. Sort the Sales column from highest to lowest and lowest to highest
2. Sort the Manufacturer column from A to Z, Z to A

Creating Multiple Measures
Multiple Measures such as Sales, Profit and Order Quantity can also be in a report
structure.
1. Add the Profit and Order Quantity fields to Values

19 | P a g e

Power BI Boot Camp

Challenge 1
The Sales Manager would like to know the answer to the following questions:
1. Which Manufacturer has the highest Sales?

2. Which Product Category has the lowest Profit value?

3. Which Channel has the highest Cost of Sales?

4. Which Manufacturer has the highest Profit?

5. Which Promotion Name has the highest Sales?

6. Which Product Sub Category has the highest Profit?

20 | P a g e

Power BI Boot Camp

Formatting Options and Table Styles
New table styles have been introduced that makes it easy to format tables quickly
and easily in Power BI.
1. Create a Table with Manufacturer, Sum of Sales, Sum of Profit and Sum of
Order Quantity
2. Select the Format icon
3. Select Table Style – Experiment with different Table Styles
4. Select the Bold Table Style
5. In Column Headers – Change the Font Colour to Black and the
Background to light blue
6. In the Values select the same light blue for the background and alternating
background colours
7. In the Total section also select the same light blue for background

Conditional Formatting
A new conditional formatting option has been added to Power BI. The conditional
formatting feature allows the background colours to be changed to range of colours.
1. Select the Fields icon
2. Select the Sales field
3. Select Conditional Formatting

21 | P a g e

Power BI Boot Camp

The Conditional Formatting dialog box opens.

1. Set Maximum to Highest Value and select a green color
2. Set Minimum to Lowest Value and select a red color

The Sales values are now formatted according to the range of colours that are in the
conditional formatting. Experiment with different colours and also use diverging.

22 | P a g e

Power BI Boot Camp

Different Methods of Aggregation
The method of aggregation can be easily changed for numeric fields in Values. The
method of aggregation allows different calculations to be easily created .
The following methods of aggregation are supported:


Sum



Average



Minimum



Maximum



Count (Distinct)



Count



Standard Deviation



Variance



Median

In this example the Sales Manager would like to know the what is the Total Sales,
Avg Sales, Highest Sale, Lowest Sale, Number of different types of Products
sold and Number of Orders processed for each Product Sub Category.
1. Drag the Product Sub Category on to the canvas
2. Drag four copies of the Sales field into the Table
3. Drag the Product Name field into the Table
4. Drag the Order ID field in to the Table
5. Change the method of aggregation by clicking on the dropdown icon next to
the Sales fields

The Distinct Count aggregation is particularly powerful. The Distinct Count function
will go through the data set and count each unique occurrence of the data in the set.
So for example in this case Power BI will count how many Products each Product
Sub Category has. In the second case each unique Order ID will be counted.

23 | P a g e

Power BI Boot Camp

The Table is created with a detail summary of the calculations.

24 | P a g e

Power BI Boot Camp

Challenge 2
The Sales Manager would like to see the following reports.
1. Create a new Table showing total Sales by Product Name
o Sort the Table from highest Sales to lowest Sales. Which Product
has the highest sales?

o Sort the Table from lowest Sales to highest Sales. Which Product
has the lowest sales?

o Which Product is sold in the most Cities?

2. Create a new Table showing total Sales by Country
o Change the summarisation method to:

25 | P a g e



Average – Which Country has the highest average Sales?



Min – Which Country has the lowest minimum Sales?



Max – Which Country has the highest max Sales?

Power BI Boot Camp

3. Create a new Table displaying the Profit by City
o Which City has the highest number of transactions?

o Which City has the lowest average Profit?

o Which City sells the most different types of Products

26 | P a g e

Power BI Boot Camp

Tables and Cards
Creating a Matrix Table
The Matrix Table allows the user to add fields to Rows and Columns within a Table
structure. Users who are familiar with Pivot Tables will recognise the same
approach to using Rows and Columns.
1. Create a new Table displaying Sales by Manufacturer
2. Add the Region field to the Table
3. Change the Table to a Matrix
4. Place the Region field in Column

Multiple levels of reports can be easily created by placing fields in a nested
hierarchy.
5. Place the Channel field after the Manufacturer field in Rows

27 | P a g e

Power BI Boot Camp

Creating a Multi Row Card
The Multi row Card is an effective Table for displaying key metric calculations for
groups of data such as Region, Product Category and Manufacturer.
1. Clear any existing Tables
2. Create a Table displaying Region, Sales, Profit and Order Quantity
3. Change the Table to a Multi Row Card
4. Experiment with the different Font Sizes and other formatting options

Creating a Card
The Card is also used to display key metric calculations. The Card visualization is
only able to display one field.
1. Delete the Multi Row Card
2. Click on the Card visualization and then select Sales
3. Repeat the process for Profit and Order Quantity

Experiment with the Data Label display and level of precision.

28 | P a g e

Power BI Boot Camp

Challenge 3
Experiment with different types of aggregations.
1. Create the following Card visualizations
a. Sum of Sales
b. Average Sales
c. Highest Sale
d. Lowest Sale
e. Number of Products that have been sold
f. Number of Countries that Products have been sold in
2. Create the following Table
a. Display for each Product Category the following
i. Total Profit
ii. Average Profit
iii. Highest Profit
iv. Lowest Profit
v. Number of Products that are Sold
vi. Number of Countries that the Products are Sold in
3. Create the following Matrix Table
a. Profit by Product Category and Region
b. Order Quantity by Manufacturer and Channel
4. Create the following Multi Card visualizations
a. Sales and Profit by Product Category
b. Sales, Profit and Order Quantity by Channel

29 | P a g e

Power BI Boot Camp

Quick Calc Option
The Quick Calc option allows the user to easily create percentage calculations from
tables of data.
1. Create a Matrix Table
a. Product Category in Row
b. Channel in Column
c. Sales in Values
In this example we now want to understand how much each value contributes to the
total.
2. Select the Sales field and Select Quick Calc

The Quick Calc dialog option is displayed.

3. Select Show Value as – Percentage of Grand Total

30 | P a g e

Power BI Boot Camp

Filtering Data
Power BI provides a number of powerful methods to filter data in reports. These
methods include the following:


Slicers



Table filters



Page filters



Report filters



Cross filters (from specific visualizations)

Slicers
The Slicer is a powerful filtering mechanism that is added within the report
1. Create the following Card visualizations
a. Sum of Sales
b. Sum of Profit
c. Average Sales
d. Number of Products Sold
e. Number of Countries Sold into
f. Use a Height of 150 and Width of 150
2. Create a Multi Row Card displaying Sum of Sales, Sum of Profit for each
Region
3. Create a Slicer for Product Category
4. Add another Slicer for Channel
5. Change the Font Size on the Slicers

31 | P a g e

Power BI Boot Camp

Filters
There are three types of filters that can be applied within Power BI. The Visual
Filters will filter the Tables and Visualizations for the current Table. The Page filters
will filter all the visualizations on a specific page and the Report filters will filter all the
Pages within a Report.

Visual Filters
Visual filters will filter a specific table or visualization.
Text Filters
Text filtering allows simple filtering whereby the user can select individual items or
more advanced filtering such as items that Start With or Contain specific strings of
text.
1. Create a Table displaying Sales by Country
2. Use the filter to find the following Countries – United Kingdom, Portugal
and Canada

3. Filter to display all Countries starting with I
a. Select the Advanced Filter and enter Starts With I
b. Click on the Apply Filter button

4. Filter the Table to display all Audio in the Product Category field

32 | P a g e

Power BI Boot Camp

a. Drag the Product Category field to Filters
b. Select Audio
c. Experiment with different Product Categories
Numeric Filters
Numeric filters make it easy to filter tables and visualizations according to numeric
values. Numeric values use logic such as is equal to, greater than and less than.
1. Display all Countries with Sales greater than 500 000

2. Display all Countries with Sales greater than 100 000
3. Display all Countries with Sales less than 80 000
Dates Filters
Date filters use a date fields to filter the data. Data filters can use logic such as is
on, is after or is before.
1. Drag the Order Date to the Visual Filter
a. Select all dates after the 1 January 2013

2. Drag the Year field to the Visual Filter
3. Drag the Month field to the Visual Filter

33 | P a g e

Power BI Boot Camp

Page Filters and Report Filters
Page Filters will filter all the tables and visualizations that are on a specific Page in
Power BI and Report Filters will filter data across all the Pages in a Power BI Report.
1. Go back to the first page of the report

2. Delete any additional tables
3. Create a New Page
4. Create a Table displaying Sales by Manufacturer
5. Drag Country to Page Filters
6. Experiment with the following:
a. Canada
b. United Kingdom
c. Canada and United States
d. All Countries beginning with I

34 | P a g e

Power BI Boot Camp

Challenge 4
The Sales Manager would like you answer the following questions:
1. How many Cities have Profit over 150 000 and what is the Total Profit?

2. What is the Total Profit for Cities that begin with the letter S?

3. Create a table displaying the Sales for each Product Name. What is the
Total Sales for Audio (Product Category) and how many Products are in
the Audio Category?

4. How many Products (Product Name) contain the word Contoso and have
Sales greater than 100 000?

5. What was the Total Sales for 1 Jan 2013 to 30 June 2013?

6. How many Countries sold over 50 different types of Products (Product
Name)?

35 | P a g e

Power BI Boot Camp

Visualizations
Power BI provides a wide range of different visualizations which assist in
understanding, interpreting and gaining insight from your data. It is important to
learn what types of visualizations should be used for what type of data. For
example, if you want to know which items are biggest or smallest then a column or
bar visualization is a good option. If you want to know what the trend is for a specific
item over time, then a line or area visualization will be a good option.

Column Graphs
Column graphs are the most effective method for displaying which items are highest,
which are lowest and how much larger one item is than another. Column graphs
therefore allow us to easily understand the magnitude difference between items,
Column and Bar graphs are therefore used for comparison analysis. Comparison
analysis is the most common form of analysis. Whenever we do reports such as
Sales by Branch; Sales by Product Category or Sales by Customer Name; we are
preforming comparison analysis.
1. Create a Table displaying Sales by Product Category
2. Change to a Clustered Column chart

Review the Formatting Options


X Axis – Review the X axis options



Y Axis – Review the Y axis options



Reference Line – Create a reference line on the graph. Enter a value of 11
000 000.



Data Colours – Change the colour of the data bars



Data Labels – Turn on and change the font to size 12 and the decimals to 1

36 | P a g e

Power BI Boot Camp



Title – Turn on the Title of the graph and other formatting options



Background – Change the background colour and transparency level



Lock Aspect –



General – Change the position, height and width of the graph



Border – Place a border around the graph

More Than One Measure
More than one Measure can be added to a visualization. For example, you could
have Sales and Profit both on your graph.
1. Add the Profit field to the Values

More Than One Dimension
It is also possible to have more than one Dimension in a visualization. Power BI will
only display one field at a time. To access the additional fields; the drill down
capability is used. The drill down capability is activated in the top left hand corner of
the visualization.
1. Drag the Product Sub Category to the Axis under the Product Category

37 | P a g e

Power BI Boot Camp

The Legend
The Legend allows an additional field to be added to the graph. In this example the
Region field is added which displays the Sales for each Product Category and also
for each Region. The Legend allows the user to highlight specific columns of data
within the graph.
1. Remove the Profit field
2. Drag the Region field to the Legend

3. Select the Asia Region on the Legend. The Asia will be highlighted on the
graph

38 | P a g e

Power BI Boot Camp

Stacked Bar Graphs
The Stacked Bar graph displays the combined values for the legend. Therefore, it
displays the total for the Product Category and how much each Region contributes to
the overall total.
The Stacked Bar graph is useful for displaying how much item has contributed to the
total.
1. Change the Clustered Column to Stacked Column

2. Turn the Data Labels on
3. Experiment with highlighting the different Regions

39 | P a g e

Power BI Boot Camp

100% Stacked Column Graph
The 100% Stacked Column graph provides a new method to visualize how much a
specific item is contributing to the total of the item. In this example you are able to
visualize how much each Region contributes to each Product Category. This
provides a powerful method of understanding the relative contribution of each
Region to the total of the Product Category.
1. Change the visualization to the 100% Stacked Column
2. Turn on the Data Labels
3. Use the Legend to highlight items

Working with Slicers
Slicers add new functionality to the visualizing the data. In this example the Reseller
channel is selected and the relative contribution of each Region is displayed for each
Product Category.
1. Add Channel to the Canvas
2. Change to Slicer
3. Review the Formatting options for the Slicer
a. Review the Selection options
b. Change the Font Size to 18

40 | P a g e

Power BI Boot Camp

Cross Filtering Between a Column Graph and Bar Graph
Power BI provides powerful cross filtering capabilities between graphs and other
graphs; and graphs and tables.
The Bar graph provides the same capabilities as a Column graph and is also used
for Comparison analysis. The Bar graph is a Column graph that is horizontal.
1. Remove the Slicer
2. Create a Bar graph displaying Profit by Channel
3. Select the Store Channel

The graph can also be filtered by selecting one of the Product Categories and
Regions.
1. Click in the white space of the Bar graph
2. Turn on Data Labels for the Bar graph
3. Select the Computer Product Category and North America Region

41 | P a g e

Power BI Boot Camp

Filtering and Highlighting
The default behaviour is for the graph that is being filtered is to highlight filtered data.
The default can be changed to filter the second graph or to have no interaction. The
Edit Visual Interactions button allows the user to select how each of these
interactions occur.

1. Click the Visual interactions button
2. Select the Sales by Product Category and Region graph

The Bar graph will display the Filter, Highlight or No Selection options.
3. Experiment with each of the three options

42 | P a g e

Power BI Boot Camp

Challenge 5
The Sales Manager has requested the following report to be created:
1. A 100% Stacked Column graph displaying Sales by Manufacturer with
Channel in the Legend
2. A Profit by Region Bar graph

3. Edit the 100% Stacked Column graph so that it is filtered when an item on
the Bar graph is selected

43 | P a g e

Power BI Boot Camp

Graph Options
Using Color Gradients
Colour Gradients are a really useful feature to highlight your data according to a
different Measure that is not currently part of the graph. For example, you may want
to create a Column graph displaying Sales by Manufacturer, but then color the graph
according to the Order Quantity sold or the Profit.
1. Create a Column graph displaying Sales by Manufacturer
2. Place Order Quantity in the Color Saturation box

Changing the Color Saturation Gradient
The color saturation colours can be changed in the Format menu option.
1. Select Data Colors under the Format menu option
2. Turn diverging on
3. Select Red as the Minimum, Yellow as Centre and Green as Maximum

The resulting graph will display the columns as the selected colors.

44 | P a g e

Power BI Boot Camp

Changing the Rules for Colors
Underneath the data color options there is the ability to enter values for the Green,
Yellow and Red. Firstly, let’s review the actual Order Quantity for each
Manufacturer.
1. Create a new Table displaying Order Quantity by Manufacturer
After reviewing the Table lets decide that all Manufacturer who have sold over 18000
are Green, all Manufacturers between 10000 and 18000 are Yellow and less than
10000 are Red.
2. Enter 10000 into Minimum
3. Enter 18000 into Maximum
4. Enter 14000 into Centre

45 | P a g e

Power BI Boot Camp

Accessing the Data behind a Graph
Graphs are great at providing a visual overview of the data and many insights can be
created from the use of visualizations. However sometimes you may also wish to
view a table of the data for the visualization.
1. Create a Column graph displaying Sales by Manufacturer
2. Select the Focus Mode icon
The Focus mode icon will increase the size of the visualization to be the size of the
page.

3. Select the See Data icon

The data is displayed in a Table format and can be displayed in a horizontal or
vertical view.

46 | P a g e

Power BI Boot Camp

Export Data
Data can be exported from any table or visualization into .csv files.
1. Select the More Options in the Top Right hand corner of the graph
2. Select Export Data

The Save dialog box will open and allow the user to enter a file name for the data.
3. Enter ManufacturerSales as the file name

Sorting Options
In the top right hand corner under More Options Power BI also provides the ability to
Sort by different fields.
1. Select the More Options in the Top Right hand corner of the graph
2. Select

3. Experiment with Manufacturer and Sales

47 | P a g e

Power BI Boot Camp

Challenge 6
Please create the following visualization:
1. Create a Column graph displaying Sales by Product Sub Category
2. Use Profit in Color Saturation
3. Set the following parameters for Data Colors:
a. Maximum – Green – 3,000,000
b. Centre – Yellow – 2,000,000
c. Minimum – Red – 1,000,000
4. Select the Focus Mode
5. Select See Data
6. Experiment with the different orientations
7. Experiment with the Sort By option and Export Data

48 | P a g e

Power BI Boot Camp

Trend Analysis – Time Intelligence
Trend analysis is the ability to see how data changes over time. By analysing how
data changes over different time periods; new insight can be gained such as; is
Sales climbing, decreasing, staying steady or volatile.
Line charts are effective at displaying how data changes over time. In the data set
you will see that there are the following fields - Order Date field, Year and Month.
The Year and Month field were created earlier in the course and later in the course
we will see how we can create custom fields to represent different elements.
1. Remove any existing graphs
2. Drag the Order Date and Sales fields into the canvas
3. Select the Line graph

4. Select the Drill Up and Drill Down options

The Inline drill down allows the user to easily drill down to a combination of your
dates. For example, Year and Quarter or Year, Quarter and Month.

49 | P a g e

Power BI Boot Camp

Time intelligence functions also works well with Slicers.
5. Create a Slicer for Year

Trend Lines
Linear trend lines can be added to the line graph.
1. Select the Analytics pane – Select Trend line
2. Add a new trend line to the line graph

50 | P a g e

Power BI Boot Camp

The new trend line is added to the line graph. Experiment with Slicers and also
placing different fields in the Legend.

Forecasting
Forecasting provides a future view of data going forward. Forecasting uses the past
data to calculate the likely future values for the data.
1. Select the Analytics pane – Select Forecasting

2. Enter the following:
a. Forecast length 10 points i.e. Produce a forecast 10 time intervals into
the future
b. Ignore the last 3 points. This is useful when you do not have complete
periods of data and you want to ignore them
c. Experiment with different confidence intervals. Confidence interval is a
calculation that provides a level of accuracy to the data

51 | P a g e

Power BI Boot Camp

Experiment with different slicers and legends.

52 | P a g e

Power BI Boot Camp

Area Graphs
The Area graph is a useful analysis tool. The Area graph combines the line graph
and also colours in the area like a column graph. This is useful as the line provides
an understanding of the trend and the coloured area provides an understanding of
the magnitude of contribution.
1. Change to the Area graph

2. Place the Channel field into Legend

3. Change to the Stacked Area graph

53 | P a g e

Power BI Boot Camp

Additional Graph Types
Combination Graph
The combination graph allows a column and line graph to be combined into one
graph. For example, you may want to display Sales and Profit by Product
Category and to display the Sales as a column and Profit as a line.
1. Create a Table with Sales and Profit by Product Category
2. Change the visualization to Combination Graph

3. Move Product Category to Column Values
4. Drag Channel to Shared Axis
5. Experiment with a Stacked Column Combination Graph

54 | P a g e

Power BI Boot Camp

Pie and Donut Graphs
Pie and Donut graphs are normally used to show how much an item contributes to
the total. Pie and Donut graphs should therefore be used to display and visualize
percentages. Pie and Donut should also only be used with a few items otherwise
they become difficult to read and understand.
1. Create a Table displaying Sales by Region
2. Select the Pie graph

3. Experiment with the Donut graph
4. Experiment with cross filtering the Pie graph with a Column graph displaying
Sales by Product Category

Treemap Graph
The Treemap graph is another form of visualization that displays the magnitude of
the item via the size that is represents in the graph.
1. Create a Table displaying Profit by Product Category
2. Select the Treemap visualization

55 | P a g e

Power BI Boot Camp

Geographical Analysis - Mapping
Geographical analysis allows the user to easily plot data such as Country, City,
States and Zip Codes on a Map. PowerBI uses the Bing mapping engine to
automatically geocode data and to place it on the map.
1. Place Country and Sales on the canvas
2. Change the visualization to Map

3. Experiment with Channel in Legend
4. Place Profit into Color Saturation

56 | P a g e

Power BI Boot Camp

Challenge 7
Please create the following visualizations:
1. Create a Line graph displaying Profit by Month with Year as a Slicer
2. Create an Area graph with Order Quantity by Month and by Product
Category with Years as a Slicer
3. Create a Combination graph displaying Sales (Column) and Profit (Line) by
Product Category
4. Create a Pie graph displaying Profit by Region
5. Create a Donut graph displaying Order Quantity by Channel
6. Create a Treemap graph displaying Order Quantity by Region and Country
7. Create a Map displaying Profit by City and Channel

57 | P a g e

Power BI Boot Camp

ScatterPlots and BubblePlots
Scatterplots and Bubbleplots are great visualizations for understanding correlations
between data. If there is a correlation between as one variable increases, there will
also be an increase in the secondary variable. For example, if the Sales value
increases we would expect that there would be a corresponding increase in Profit.
Scatterplots are also useful for identifying outliers in data. For example, you may
have items with high Sales and low Profit. These maybe items that you would want
to focus on.
1. Select the Scatterplot visualization
2. Place Sales into X-Axis and Profit into Y axis
3. Place Product Category into Details
A data point is created for each Product Category on the Scatterplot.

The size can also be used to create a bubbleplot visualization.
1. Place Order Quantity in the Size field
2. Turn Category Label on

58 | P a g e

Power BI Boot Camp

In this example we are going to use the Product Name field with the Manufacturer as
a Slicer. This allows the user to easily pick up trends within the data for different
Manufacturers. Use the tooltip to display the details of specific data.

The Play Timeline
The Play box allows a date orientated field to display the change of data over time.
1. Place Product Sub Category in Details
2. Sales in X-Axis and Profit in Y-Axis
3. Order Quantity in Size
4. Use Product Category as a Slicer – Select
5. Place Year in Play
6. Turn Category Labels on

59 | P a g e

Power BI Boot Camp

Challenge 8
1. Load the World Bank Indicators data file
2. Create a Scatter graph comparing Life expectancy at Birth to Finance GDP
– use the Average aggregation
a. Place Year in Play axis
b. Place Country in Details
c. Place Population Total in Size

3. Compare Health Mortality under 5 and Population Urban Count

4. Compare Business Phone Subscriptions and Business Internet Users

60 | P a g e

Power BI Boot Camp

Creating an Interactive Dashboard
In this activity you are going to create the following interactive dashboard.

1. Create the following Card Visualizations
a. Total Sales
b. Total Profit
c. Average of Sales
d. Average of Profit
e. Number of Products Sold (Product Name)
f. Number of Cities that have been Sold into (Cities)
2. Column and Line Graph displaying Sales (Column) and Profit (Line) by
Month
3. Bar Graph displaying Sales by Product Category and Profit is used for color
saturation
4. Sales and Profit by Region – Column graph
5. Profit by Channel – Donut Graph
6. Year as a Slicer
7. Sales and Profit as a Scatterplot with Product Sub Category in Details
8. Add rectangle with different colors to create backgrounds
9. Add a Text box to create a heading

61 | P a g e

Power BI Boot Camp

Publishing the Dashboard
Dashboards are published to the Power BI Cloud service.
1. Select the Dashboard created in the previous exercise
2. Select Publish Dashboard
3. Sign into Power BI

The Power BI Desktop sign in will appear. Enter your Power BI login and password
details. Power BI will publish the Reports and Data Set to the Cloud Power BI.

1. Login to powerbi.com
You will notice that the data set has been uploaded and that the report is available.
The report and data set will be called the same name as you saved the Power BI
workbook.

62 | P a g e

Power BI Boot Camp

Pinning Visuals to a Dashboard
Tables and visualizations are pinned to dashboards. An entire report can be pinned
to the dashboard using the Pin Live Page option.

To Pin Individual Tables and Visualizations
1. At the top of the visualization – Select Pin Visualizations for the Total Sales

2. Create a new Dashboard – Dashboard 1

3. Repeat the process for Total Profit, Sales and Profit by Month and Sales
and Profit by Product Category – Select Existing Dashboard – Dashboard1
4. Go to the Dashboard screen

63 | P a g e

Power BI Boot Camp

Q & A Feature
The Q & A feature allows users to easily ask questions about the data. Enter the
following questions into the Q & A feature:
1. Total Sales
2. Total Profit
3. Profit by Channel as Pie
4. Total Sales for United States
5. Total Profit for China
6. Total Sales by Country as Table
7. Total Profit and Total Sales by Country as Table
8. Distinct Count Product Name by City
9. Total Profit by Product Category as Donut
10. Total Sales by Region as multi row card
11. Distinct Count of Product Name by Country as Table
12. Experiment with your own questions

64 | P a g e

Power BI Boot Camp

Inviting Other Users
The Share function allows you to invite other users to use the dashboard. In this
example you are going to invite a test user to view your dashboard.
1. Select the Share icon
2. Enter test@powerbicollege.com into the email address

3. Click Share
The user will receive an email stating that a Power BI dashboard has been shared
with them.

65 | P a g e

Power BI Boot Camp

Accessing a Shared Dashboard
1. Go to Powerbi.com site and login as test@powerbicollege.com

The Dashboard that has been shared is available and the user is also able to use the
Q & A feature.
2. Ask the following questions using Q & A
a. Total Sales by Product Category as Column
b. Total Profit by Region as Treemap
The user is also able to create their own visualization and tables using the fields and
visualizations which are to the right hand side.

3. Create the following tables and visualizations
a. Matrix displaying Sales by Product Category and Region
b. Stacked Column displaying Profit by Region and Channel
c. Table displaying Total Sales, Total Profit, Average Sales, Highest Sale
and Lowest Sale by Product Name

66 | P a g e

Power BI Boot Camp

Power BI on Mobile
Power BI is available on Windows 10 tablets, Android and iPad. Each version
requires an App to be downloaded and installed on the device. The app will require
you to login to your account and will then display a list of workspaces that you have
access to.
To view a video on the Windows 10 Power BI app – click here
https://www.youtube.com/watch?v=K01B4MEV7e8

To view a video on the Android Power BI app – click here
https://www.youtube.com/watch?v=BJHodVeyZFE

To view a video on the iPad app – click here
https://www.youtube.com/watch?v=zeadB8c_NyE

67 | P a g e

Power BI Boot Camp

Assignment
The management of your company would like you to analyze the Employee Master
spreadsheet file. In the spreadsheet file there are two sheets – Master and
Workshops. In this assignment you will only be using the Master sheet.
The management would like you to create the following Cards, Tables and
Visualizations in the Power BI Desktop and then to publish the resulting dashboard
to the Power BI cloud.
Please create the following dashboard:
3. A Column graph displaying the Number
1. Cards displaying the following:
a. Total number of Employees
b. Average Age
c. Number of Female Employees
d. Number of Male Employees
e. Total Salary
f. Average Salary

of Employees per Job Grade
4. A Bar graph displaying the Number of
Employees per Department
5. A Pie graph displaying Number of
Employees by Gender
6. A Pie graph displaying the Number of
Employees by Race

2. A table displaying for each Department
the following:
a. Total Salary
b. Average Salary
c. Highest Salary
d. Lowest Salary
e. Number of Employees

68 | P a g e

Power BI Boot Camp

Enhancing Power BI - DAX Formulas
DAX Formula Language
DAX stands for Data Analysis Expressions and is the new formula language for
PowerPivot. DAX can use common operators such as addition, subtraction and also
includes 135 different functions with a number of functions being similar to Excel.
To add DAX formulas a new column is added to PowerPivot. Each column may only
have one DAX formula.
Operators in DAX
DAX uses the standard operators found in Excel. Which makes it easy to create
calculations using Excel type logic.


+ addition, - subtraction, * multiplication and / division



& for text concatenation



^ for exponents



=, >, <, >=, <=, <> as comparison operators



&& creates an AND condition between two expressions that each have a true
/ false result



|| creates an OR condition between two expressions that each have a true /
false result



! is a NOT operator. Will turn true into False and False into True

69 | P a g e

Power BI Boot Camp

Simple Calculations in DAX
In this exercise we are going to review how to create simple calculations such as
multiplication, division, addition and subtraction. In this data source we have to
multiply the ListPrice and OrderQty to get the total Sales. We also need to multiply
the UnitPrice with the OrderQty to get the total Cost and then deduct the Cost from
the Sales to calculate the Profit.
1. Load the TrainingSample2.xlsx file into Power BI Desktop Designer
2. Select Table1 and Select Load
3. Go to Data – Select the Modelling menu option

1. Select New Column
2. Sales = [OrderQty]*[ListPrice]
Create new Columns for Cost and Profit
3. Select New Column
4. Cost = [UnitPrice]*[OrderQty]
5. Select New Column
6. Profit = [Sales]-[Cost]

70 | P a g e

Power BI Boot Camp

Creating Time Intelligence Functions
PowerPivot includes a wide range of Date functions. Date functions provide new
ability to understand your data over time, to understand trends. The Date functions
are very similar to Excel.
Common Date functions


Year([field]) – The Year of the Date field



Month([field]) – Please note that PowerPivot will return the number of the
month i.e. 1 for January, 5 for May etc.



Day([field] – The Day of the month from the Date field



Hour([field]) – The hour from the Date field



Minute([field]) – The minute from the Date field



Second([field]) – The second from the Date field



Now() – Current date and time



Today() – Current date



Weekday([Date]) – Returns a number from 1 to 7 identifying the day of the
week of a date. By default 1 is equal to Sunday



WeekNum([Date]) – Returns the week number of the Year

Create the following fields:
1. Year = Year([Order Date]) – Creates a field for Year
2. Month = Month([Order Date]) – Creates a field for the Month number
3. Day = Day([Order Date]) – Creates a field displaying the day of the month
4. WeekDay = Weekday([Order Date]) – Creates a field displaying the day of
the week
5. WeekNum = WeekNum([Order Date]) – Creates a field displaying the week
number of the year.

71 | P a g e

Power BI Boot Camp

Challenge
1. Create a Table displaying Sales by Year and WeekNum
2. Create a Column Graph displaying Profit by WeekDay
3. Create a Column Graph displaying Sales by Product Category and use
Weekday as a Slicer
4. Create an Area Graph displaying Sales by Day – Year and Month as Slicer

Formatting the Month Number
It is not easy to understand the months being formatted as 1,2,3 etc. It would be
much easier to read the Months as January, February, March etc.
1. Create a formula Month Name column
2. =Format(trainingsample2[SalesDate],”MMMM”)
Formatting the Day Number
1. Create a formula WeekDay Name column
2. =Format(trainingsample2[SalesDate],”DDDD”)
A problem with Month Name and Week Day name is that Power BI does not know
the correct order for displaying the Month Names or Week Day names and will
display them in alphabetical sequence.
1. Create a Matrix displaying Sales by Year and Month Name
2. Create a Table displaying Profit by Weekday

72 | P a g e

Power BI Boot Camp

Using the Sort by Column
A method to resolve the problem of month names and weekday names sorting
alphabetically is to use the Sort by Column option on the ribbon – under the
Modelling menu.
1. Select the Data view and the Modelling menu
2. Select the WeekDay Name column
3. Select Sort By Column
4. Select Sort By – WeekDay

5. Repeat the process for the Month Name column
6. Create a Column graph displaying Sales by Month Name and Profit by
Weekday Name

73 | P a g e

Power BI Boot Camp

Challenge
Please create the following tables and visualizations:
1. Sales and Profit by Year and Month Name
2. Area chart displaying Sales by Month Name with Year as a Slicer
3. Column Chart displaying Profit by Product Category with WeekDay Name
as a Slicer
a. Turn Data Labels on
4. Line Chart displaying Sales by Channel by Day add Month Name as a Slicer

74 | P a g e

Power BI Boot Camp

Creating a Date Table
To save time when you are creating Date orientated calculations, a master date file
can be created. This master file is then linked to the data file and the date fields can
be used in Power BI reports.
This functionality uses the Power BI ability to create relationships between tables to
allow the user to easily create date orientated tables and visualizations. We are
going to look at relationships in more detail in the next section.
1. The Date Table is created in an Excel spreadsheet

The following formulas are used to create the Date Master:


Master Date – The Master Date must be a continuous range of dates starting
with a date that is before the first date in your data source and ending with a
date that is larger than the last date in your data source



Day – Calculates the Day of the Month from the Master Date =Day formula



Month – Calculates the Month of the Year from the Master Date =Month
formula



Year – Calculates the Year of the Master Date = Year formula



Weekday – Calculates the Weekday number from the Master Date =Weekday
formula



WeekDay Name – Calculates the Name of the Weekday from the Master
Data =Text formula



Month Name – Calculates the Month Name from the Master Data =Text
formula



Week Num – Calculates the week number from the Master Data =Weeknum
formula

75 | P a g e

Power BI Boot Camp

2. The Date Master sheet is loaded into the Power BI Desktop
3. A relationship needs to be created between the Order Date and the Master
Date

4. The Sort By Column for Week Day Name and Month Name needs to be
correctly set
a. Weekday Name sorted by Weekday
b. Month Name sorted by Month

5. Create the following:
a. Area graph displaying Sales by Month Name
b. Bar graph displaying Profit by Weekday Name
c. Table displaying Sales and Profit by Year and Weeknum
d. Year as a Slicer

76 | P a g e

Power BI Boot Camp

DAX Measures
DAX measures allow more efficient and powerful calculations to be created in Power
BI. The immediate benefit of measures is that the calculation is only performed at
the result level and not the table level. If you have two million rows and create a new
calculated column, the new column will need to perform the calculation two million
times to populate the column. Whereas, a DAX measure will only create one
calculation.
For example, if we have 15 000 rows in a data source and we have a Sales column.
When we create a Report showing Sales by Manufacturer there will be 15 000
calculations. However, if we use a Measure field there would only be 10 calculations
(there are 10 Manufacturers).
There are many different types of DAX formulas, however most of the basic formulas
are very close to Excel formulas – which makes it easy to get started.
A list of common DAX formulas:


Sum - =SUM([Field])



Count - =COUNT([Field])



DistinctCount - =DISTINCTCOUNT([Field])



Average - =AVERAGE([Field])



Min - =MIN([Field])



Max - =MAX([Field])



Divide - =DIVIDE(Numerator[Field], Denominator[Field])

Getting Started
In this example you will use the Data view and the modelling menu option to create
the following Measures:
1. Total Sales = Sum([Sales]) – Set the formatting as thousands separator and
no decimal
2. Total Profit = Sum([Profit])
3. Avg Sales = Average([Sales])
4. Highest Sale = Max([Sales])
5. Lowest Sale = Min([Sales])
6. DCount Customers = DistinctCount([Customer])
7. Avg Sale per Customer = Divide([Total Sales],[Dcount Customers])
8. Profit Ratio = Divide([Total Profit],[Total Sales]) – Set as Percentage
77 | P a g e

Power BI Boot Camp

The following list of Measures will be created in your table.

In the Report view you will notice that Measures such as Total Sales and Total Profit
will be very similar to using the Sales and Profit field. You will notice with the
Measure that you are not able to change the method of aggregation. Most people
may see this as a disadvantage. However, remember that the Measure is far more
efficient in terms of number of calculations and also that Measures can be used in
other Measure calculations.
This is shown by the creation of the Avg Sale per Customer and Profit Ratio
calculations. Previously we would not have been able to create these calculations in
our reports.
1. Create a Matrix displaying Avg Sales per Customer by Business Segment
and Region
2. Create a Matrix displaying Profit Ratio by Category and Region

78 | P a g e

Power BI Boot Camp

The Calculate Formula
The Calculate formula is one of the most useful formulas in DAX. The Calculate
formula calculates an aggregation and then filters the result according to the
specified criteria. The Calculate formula is often referred to as a supercharged
SumIF statement. The syntax for the Calculate formula is as follows:
=Calculate(, ,)
The aggregate expression can include the following:


Sum



Average



Count



Min



Max



Distinct Count

The Calculate formula can therefore be used to create a number of calculations
using filters.
For Example
Calculate the total Sales for Australia
Sales Australia = CALCULATE(sum([Sales]),Table1[SubRegion]="Australia")
Create a Table displaying Business Segment and Total Sales. Add the new Sales
Australia field:

In this next example, we want to create a calculation displaying only the Profit for
2003. In this example instead of using the =Sum expression, we are able to use the
Measure within the Calculate formula.
Calculate the total Profit for the Year 2003
Profit 2003 = CALCULATE([Total Profit],Table1[Year]=2003)

79 | P a g e

Power BI Boot Camp

We can also use more than one filter within a Calculate formula. For example, we
can combine filters to calculate the Profit for Australia in 2003.
Profit Australia 2003 = CALCULATE([Total
Profit],Table1[SubRegion]="Australia",Table1[Year]=2003)

80 | P a g e

Power BI Boot Camp

Relationships
In this example we are going to use a Human Resource data source. The data
source contains two tables. The first table is a Master table and contains a list of the
50 employees. The second table contains a list of training that Employees have
attended.
In this example the Employee ID is in both fields so we are going to use the
Employee ID to create a relationship between the two tables.
1. Start a new session of Power BI
2. Load the Employee Master Excel file through the Get Data interface
Both tables are loaded into the Power BI data model. Power BI automatically picks
up that there is a relationship between the two tables and creates the necessary
relationship.

Power BI creates a many to one relationship between the Workshops table and the
Master table.
Power BI has named the tables – Master1 and Workshops2. To change the names
of the tables:
1. Double click on the Table name – Change the names to Master and
Workshop

81 | P a g e

Power BI Boot Camp

1. To edit the Relationship double click on the line that connects the two tables

2. To Delete the relationship – Select the interconnecting line and press the
Delete button
3. To Create a new relationship – Drag the Employee ID field from the
Workshop Table to the Employee ID field on the Master Table

82 | P a g e

Power BI Boot Camp

Employee Master Challenge
Fields can be used from the two tables to create an interactive dashboard. Create
the following dashboard:

1. Create the following calculations:
a. Total Training Cost – Sum of Cost
b. No of Employees – Distinct Count of Master Employee ID
c. No of Employed Employees Trained – Calculates the number of
employed employees that have attended training
d. No of Employees Trained – Counts the number of employees that
have attended training. There are 50 employees in the company, but
many have the employees have attended training more than once
e. Average Training Cost – Average of the Cost field
f. Avg Training Cost per Employee – The Total Training Cost divided
by the number of Employed Employees Trained
2. Area graph displaying Training Cost by Month
3. Area graph displaying the Number of Employees trained by Month
4. Column graph displaying the Training Cost by Dept
5. Pie graph displaying the Count of Employees who attended training by
Gender
6. Bar graph displaying the Cost by Course Name
7. Slicer displaying the Year

83 | P a g e

Power BI Boot Camp

Creating Custom Visualizations
Power BI provides an open framework for the creation of new visualizations which
can be imported into Power BI and used in reports and dashboards.
Power BI Visualization Gallery
1. Go to https://app.powerbi.com/visuals/

In this example we are going to download a Box and Whisker plot visualization.
2. Select the Box and Whisker (Brad) visualization and download
In Power BI Desktop go to the Visualizations option and select the last option which
displays ellipses.
3. Select Import Visual – Select the Box and Whisker visualization that has
been downloaded
The new visualization is displayed on the visualization display.

84 | P a g e

Power BI Boot Camp

Creating a Custom Visual
1. Delete the Bar graph displaying the Cost by Course Name
2. Click on the Box and Whisker visualization to select it
3. Place Dept in the axis box and Age in values

4. Experiment with Salary
5. Filter the Box and Whisker using other options such as Gender

85 | P a g e

Power BI Boot Camp

Using the Related Function
The related formula is useful when you want to use the fields from other related
tables.
In this example we are going to use the related formula to display the Department
and Gender of the Employee in the Workshop table.
1. Go to the Data view and Select the Modelling menu option
2. Select the Workshops table
3. Select New Column -Enter the formula
4. Department = RELATED(Master[Dept])
5. Select New Column - Enter the formula
6. Gender = RELATED(Master[Gender])

86 | P a g e

Power BI Boot Camp

Enhancing Your Power BI Data
In this last section we are going to review some features that allow you to edit,
manipulate and transform your data in Power BI. These features are available in the
Power BI Desktop and are available under the Edit Queries icon.
Anybody who is familiar with Excel Power Query will recognise the features. The
features in Power BI Edit are exactly the same as Power Query.

Transforming Data
A common task in data analysis is converting and transforming your data so that you
can easily create reports and dashboards. Common examples of transformations
include:


Changing the data type i.e. Text, Numeric and Date



Renaming field names



Changing the case of text fields



Replacing values, for example M is Male



Creating new fields, for example Year and Month from a Date field

In this example we are going to use the Employee Data.csv file
1. Select Get Date – Select Employee Data.csv – Select Edit
2. Convert Employee ID to Text data type
3. Remove Columns – NationalIDNumber, ContactID and LoginID
4. Rename the Title field to Job Position
5. Change the Birth Date to data type Date and locale – English United
Kingdom
6. Change Marital Status to M – Married and S – Single
7. Change Gender to M – Male and F – Female
8. Change the Hire Date to data type Date and locale – English United
Kingdom
9. Remove the Modified Date field
10. Change the Job Position field to be in Upper Case and to also use the Trim
formula

87 | P a g e

Power BI Boot Camp

Aggregating Data into New Tables
A common requirement when you are working with data is to produce a work with
summarised data tables. For example, in our current Employee Master example we
may want to create a table that stores the total training cost, highest training cost and
lowest training and no of courses attended for each Employee.
1. Select the Edit Queries icon
2. The Query Editor interface is opened
The Query Editor uses the same ribbon and menu structure that the rest of Power BI
Desktop uses.

The first requirement is that we require the Employee Name to be in the Workshops
table. We are going to use a Merge Query option to access the Employee Name.
1. Select the Workshops table
2. Select Merge Queries
The Merge Queries dialog box opens.

88 | P a g e

Power BI Boot Camp

3. Make sure that Employee ID in the Workshops table is selected and the
Employee ID in the Master Table
4. Click Ok

A New Column is created in the Workshops table. Select the NewColumn icon to
select the fields to add to the table.

5. Select Employee Name and remove the Use original name as prefix option
The Employee Name is added to the Workshops table.
6. Select the Group By icon
The Group By dialog box opens.

7. Group by – Employee Name
8. Training Cost – Sum – Cost
9. Number of Courses – Count Rows

89 | P a g e

Power BI Boot Camp

10. Avg Training Cost – Average –Cost
11. Click Ok

Working with Queries and Applied Steps
If you want to keep the original Workshops table, it can be duplicated.
1. Right Click on the Workshops table

90 | P a g e

Power BI Boot Camp

A list of options is displayed. Select the duplicate option. The table is duplicated.
2. Select the original Workshops table
A list of all the steps that have been applied to the data is displayed.

3. Delete the last step – Grouped Rows
The Workshops table is returned to the state that it was at before the grouping.
4. Select the Workshops(2) query
5. Change the Properties name to Train Cost by Employee

91 | P a g e

Power BI Boot Camp

Analysis with Excel
In this lesson we are going to learn how to analyse data in Excel. Power BI provides
us with the capability to create powerful online data models using the tools that we
have covered in the course so far. These data models actually create a SQL
Analysis Services database in the background. These data models can therefore be
extremely powerful in developing and creating custom Excel analysis.
In this example we are going to use the Employee Master data that we have been
using in previous exercises.
1. Start a new version of Power BI Desktop
2. Load the Employee Master data – Select the Master1 and Workshops2
tables
3. Publish the data to the cloud
4. On the cloud data set select Analyze with Excel

The system will prompt you to download the SQL_AS_OLEDB software. Please
download and install the software. The software creates the capability for Excel to
read the data from the Power BI data set.
Once the software has been download the system will prompt you to download the
Microsoft Office Data Connection file. Download this file
1. Start Excel
2. Open the data connection file that was downloaded
3. Excel will provide a warning that there is a security concern. Enable the file
4. An Excel spreadsheet will open with a Pivot Table

92 | P a g e

Power BI Boot Camp

The table from the Employee Master file will be opened.
1. Try to create a report displaying the Cost (workshops table) by Department
(Master table)
The Pivot Table does not allow the Cost field to be placed in the Values box. This is
due to the fact that the Cost field has to be created as a Measure in the Power BI
data model.

93 | P a g e

Power BI Boot Camp

Creating Measures in the Data Model
Earlier in the course we reviewed how to create Measures in the Power BI Desktop.
1. Go to the Power BI Desktop
2. Select the Modelling tools
3. Select New Measure

Enter the following Measures into the Workshop Table
1. Total Cost=Sum(Workshops2[Cost])
2. Avg Cost = AVERAGE(Workshops2[Cost])
3. Publish the data set
Go back to Excel and refresh the data set. To refresh the data set, select Pivot
Table Tools and the Analyze menu option. Select the Refresh icon. The new
Measures will now be added to the list of fields under the Measures icon.

Create the following Pivot Tables:


Total Cost for each Department



Total Cost and Avg Cost for Department



Total Cost by Course Name



Total Cost by Department and Gender

94 | P a g e

Power BI Boot Camp

Experiment
Create the following Measures in Power BI Desktop:


Avg Age = Average(Master1[Age])



Highest Age = Max(Master1[Age])



Lowest Age = Min(Master1[Age])



Total Salary = Sum(Master1[Salary])



Avg Salary = Average(Master1[Salary])



Highest Salary = Max(Master1[Salary])



Lowest Salary = Min(Master1[Salary])

Refresh the data into Excel and create the following Pivot Tables:


Avg Age, Highest Age and Lowest Age by Department use Gender as a Slicer



Total Salary, Avg Salary, Highest Salary and Lowest Salary by Department



Avg Age, Avg Salary and Avg Cost by Course Name use Department as a
Slicer

95 | P a g e

Source Exif Data:

File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.5
Linearized                      : No
Page Count                      : 95
Language                        : en-US
Tagged PDF                      : Yes
Title                           : Business Data Analysis with Excel 2007 / 2010
Author                          : IanLJ
Creator                         : Microsoft® Word 2016
Create Date                     : 2016:10:20 08:44:08+02:00
Modify Date                     : 2016:10:20 08:44:08+02:00
Producer                        : Microsoft® Word 2016

EXIF Metadata provided by EXIF.tools

Business Data Analysis With Excel 2007 / 2010 Power BI Boot Camp Learner Guide

Power-BI-Boot-Camp-Learner-Guide

Navigation menu

Versions of this User Manual:

Views

Navigation