Data Analysis with Microsoft Power BI

Module 7 – Create Reports

Institute of Advanced Technologies (IAT)

Optimize Model Performance

Design a Report Enhance the Report

Introduction Introduction
Apply slicing, filtering and sorting
Choosing effective visualizations

Adding visualizations to reports Add buttons, bookmark and selections

Key Performance Indicators
Import a custom visual
Publish and export reports
Format and configure visualizations

Basic Interactions
Configure conditional formatting

Design report navigation

2
 Introduction
• Power BI visuals are attractive charts and graphics that you can use to revitalize your data.
• Visuals allow you to share data insights more effectively and increase comprehension, retention, and appeal.

• Visuals are a fundamental part of your report because they help your report audience connect and interact with the information to make informed
decisions quickly.

• By using a visual approach, the company can provide users with quicker, easier access to the information that they need to make their business
decisions.

• Some key guidelines for creating a well-designed report layout

 Draw a sketch of your report layout
 You could even draw multiple sketches where you try out different ideas,

 then discuss these ideas with your team to help you choose the best layout design.

 Focus on the most important information.

 Highlight key parts of your report with a bright color or summary icon, so that it stands out, and users drawn to the most critical metrics.

 Select the right background for the context of your report.

 A white background makes your report look clean and business-like,

 A black background draws the eye to colorful highlights on the report, and an image used as a background adds numerous feeling.
 Report page

• The report users might view the reports on screens with • To access the page settings, select the white space on
different aspect ratios and sizes. your report canvas to open the Format pane.
• The default display view is Fit to page, which means that the • You can then configure the following settings to suit
contents are scaled to best fit the page. your needs: Page information, Page
• If you need to change this view, go to the View tab, alignment, Page size, Wallpaper, Page
select Page view, and then select your preferred page view background, and Filter pane.
option, as illustrated in the following screenshot.
 Choosing effective visualizations

• Power BI Desktop offers a range of out-of-the-box visualization options that are available directly from
the Visualizations pane.

• When you select the fields that you want to display in a visualization, you can experiment with all the different
visualization types to find the one that best suits your needs.

• If you can't find a visual that meets your needs, you can download other visuals from Microsoft AppSource or
import your own custom visuals.

• Depending on the type of data in your selected fields, one or more visualizations might not be suitable. For example,
geographic data will not display well as a funnel chart or line chart visualization.

• It is important that you choose an effective visualization to ensure that you display the data in the best way possible.
 Choosing effective visualizations

• There are different types of visualizations that are available within Power BI Desktop, using the
same data source for illustration purposes.

Table and Matrix

Gauge chart
Bar and column charts
Waterfall visualization
Line and area charts
Scatter chart
Pie chart, donut chart, and Treemaps
Maps
Combo charts
Slicer
Card visualization
Q&A
Funnel visualization
Table and Matrix visualizations
• Table visualization was selected by default.

• The table is a grid that contains related data in a logical series of

rows and columns.

• The table supports two dimensions, and the data is flat, which
means that duplicate values are displayed and not aggregated. It
can also contain headers and a row for totals.

• The Matrix visualization looks similar to the table

visualization;
• It allows you to select one or more elements (rows, columns,
values) in the matrix to cross-highlight other visuals on the
report page.
 Bar and column charts
• Power BI Desktop has a variety of bar and column chart
visualizations that present specific data across different
categories in a stacked or clustered format.

• The stacked format will stack the information items on

top of each other.

• For example, the following clustered column chart

shows a single column with total sales for each country,
whereas the stacked column chart shows data for sales
by country, by product name.

• All sales data is stacked into one column to show you

the total sales by country, broken down by how much
each product contributed to the overall total sales.
 Line and area charts

• The line chart and area chart visualizations are beneficial in helping you present trends over time.
• The basic area chart is based on the line chart, with the area between axis and line filled in.
• The main difference between these two chart types is that the area chart highlights the magnitude of change
over time.
 Pie chart, donut chart, and Treemaps
• The pie chart, donut chart, and Treemap visualizations show you the relationship of parts to the whole by
dividing the data into segments.

• From a data analysis perspective, these charts are not useful because interpreting the data that they present can
be difficult

• These charts are best suited for illustrating percentages, such as the top five sales by product or country, or
any other available categories.
 Pie chart, donut chart, and Treemaps
• When using pie charts, donut charts, and Treemaps, try to avoid presenting too many categories because it
results in thin slices (or rectangles) that provide no added value to the user.

• Each level of the hierarchy is represented by a colored rectangle (branch) containing smaller rectangles
(leaves). The space inside each rectangle is allocated based on the value that is being measured. The rectangles
are arranged in size from top left (largest) to bottom right (smallest).

A Treemap is ideal to visualize:

• Large amounts of hierarchical data when a bar chart can't
effectively handle the large number of values.
• Proportions between each part and the whole.
• The distribution pattern of the measure across each level of
categories in the hierarchy.
• Attributes, by using size and color coding.
• Spot patterns, outliers, most-important contributors, and
exceptions.
 Combo charts

• The combo chart visualization is a combination of a column chart and a line chart that can have
one or two Y axes. The combination of the two charts into one lets you:

Compare multiple measures with different value ranges.

Illustrate the correlation between two measures in one visual.

Identify whether one measure meets the target that is defined by another measure.

Conserve space on your report page.

 Card visualization

• The card visualization displays a single value: a single data point.

• This type of visualization is deal for visualizing important statistics that you want to track on your
Power BI dashboard or report, such as total value, YTD sales, or year-over-year change.

• The multi-row card visualization displays one or more data points, with one data point for each
row.
 Funnel visualization

• The funnel visualization displays a linear process that has sequential connected stages, where items flow
sequentially from one stage to the next.

• Funnel charts are most often seen in business or sales contexts. For example, they are useful for
representing a workflow, such as moving from a sales lead to a prospect, through to a proposal and sale.

Funnel charts are great options in the following contexts:

 When the data is sequential and moves through at least
four stages.
 When the number of items in the first stage is expected
to be greater than the number of items in the final stage.
 To calculate a potential outcome (revenue, sales, deals,
and so on) by stages.
 To calculate and track conversion and retention rates.
 To reveal bottlenecks in a linear process.
 Gauge chart

• A radial gauge chart has a circular arc and displays a single value that measures
progress toward a goal or target.

• The value at the end of the arc represents the defaulted maximum value, which
will always be double the actual value.

• To create a realistic visual, you should always specify each of the values.

• You can accomplish this task by dropping the correct field that contains an amount
into the Target value, Minimum value, and Maximum value fields on
the Visualization pane.
 Radial gauges can be used to show the progress that is being
• The shading in the arc represents the progress toward that target.
made toward a goal or target, or they can show the health of a
• The value inside the arc represents the progress value. Power BI spreads all single measure.
possible values evenly along the arc, from the minimum (left-most value) to the
 However, radial gauges do take up a lot of space in
maximum (right-most value).
comparison to the insights that they provide.
 It is more effective to use a pair of gauges with a spark line so
users can see the trend and know what to do about it.
 Waterfall visualization

• The waterfall visualization (also known as a

bridge chart) shows a running total as values
are added or subtracted, which is useful in
displaying a series of positive and negative
changes.

• The chart consists of color-coded columns, so

Waterfall charts can be used to:
you can quickly identify increases and  Visualize changes over time or across different categories.
 Audit the major changes that contribute to the total value.
decreases.
 Plot your organization's annual profit by showing various
• The initial and the final value columns often sources of revenue to help determine the total profit (or loss).
 Illustrate the beginning and ending headcount for your
start on the horizontal axis, while the organization in a year.
intermediate values are floating columns.  Visualize how much money you earn and spend each month
and the running balance for your account.
 Scatter chart
• The scatter chart visualization is effective when you are comparing
large numbers of data points without regard to time.

• The scatter chart has two value axes to show: one set of numerical
data along a horizontal axis and another set of numerical values
along a vertical axis.

• The chart displays points at the intersection of an X and Y numerical

value, combining these values into single data points.

• These data points might be distributed evenly or unevenly across the Scatter charts also allow you to show relationships between two numerical
horizontal axis, depending on the data. You can set the number of
values.
data points, up to a maximum of 10,000.
 Plot two groups of numbers as one series of x and y coordinates.
• You might want to use a scatter chart instead of a line chart because  Turn the horizontal axis into a logarithmic scale.
it allows you to change the scale of the horizontal axis.  Display worksheet data that includes pairs or grouped sets of values.
Show patterns in large sets of data, for example, by showing linear or
non-linear trends, clusters, and outliers.
 Compare large numbers of data points without regard to time.
 The more data that you include in a scatter chart, the better the
comparisons that you can make.
 Maps
• Power BI integrates with Bing Maps to provide default map coordinates (a process called geocoding), so you can create maps. Together,
they use algorithms to identify the correct location; however, sometimes, it's a best guess.

• A basic map (bubble map) is used to associate categorical and quantitative information with spatial locations.

• This type of map visual displays precise geographical locations of data points on a map, as illustrated in the following image.

• A fill map uses shading, tinting, or patterns to display how a value differs in proportion across a geographical region.

• Similarly, shape maps use colors to display relative comparisons of geographical regions. You can also use an ArcGIS map to display
graphical information in a more interactive way.
 Slicer visualization
• The slicer visualization is a standalone chart that can be used to filter the other visuals on the page.

• Slicers provide a more advanced and customized way of filtering, in comparison to the Filters pane, which is suited to
more basic filtering operations.

• Slicers are ideal to:

 Visualize commonly used or important filters on the report canvas for easier access.

 Simplify your ability 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.

• TIP: Using a slicer that is set to a drop-down format will defer the queries that are being sent to the dataset and can help
improve performance.
 Q & A visualization

• The Q&A visualization allows you to ask natural language questions and get
answers in the form of a visual.

• This ability to ask questions is valuable to consumers and to you, the report
author.

• This visualization type can help you create visuals in the report, and it can also
be used as a tool for consumers to get answers quickly.

• The Q&A visualization consists of the following four core components:

• Q&A question field: Enter your question in this box to see possible answers and
suggestions from Power BI.

• Suggestions: Choose a question from a prepopulated list of suggestions generated by Power

The example illustrates how to implement
BI.
Net sales by country.
• Convert icon: Select this icon to convert the Q&A visual into a standard visual.

• Gear icon: Select this icon to open the Q&A Tooling pane, which allows designers to
configure the underlying natural language engine.
 Adding visualizations to reports

• Power BI has a variety of visuals that you can use to report on the data in your data model.

• Visuals allow you to present the important information and insights that you discovered in the data in a compelling and
insightful way.

• The report consumers rely on these visualizations as a gateway to the underlying data.

While the visual is selected, you can change the visualization type by selecting a different visual from the Visualizations pane.
 Visuals

• You might want to use a combination of visuals in your report, such as cards, charts, tables, slicers, and so on.

• It is important to use the right number of visuals on a page, and then size and position those visuals in the best way.

Number of visuals

• Consider the number of visuals (including slicers) that you want to use on each report page.

• Also, visuals are a key factor in the performance of your report, they contribute to performance issues.

• It's best to limit the number of visuals you use on a page.

• Rather than using multiple visuals, you can provide information in other ways, such as drill through pages and report
page tooltips.
 Position of visuals
• When you add visualizations to a report, you can move those visuals to specific locations on the page, and make them
bigger or smaller for a more effective display.

• It is best practice to place the most important visual in the top-left corner of your report, as your report users will most
likely from read left to right, and top to bottom.

• To move a visualization, select any area of the visualization and then drag it to the new location.

• To evenly distribute distance between visuals located on the canvas, you can also use the Align function. Use CTRL+click
to select all of the visuals that you want to align, then select the Format tab and select Distribute horizontally.
 • The visuals will be evenly distributed against each other.

Size of visuals
 When you add a visual to a report, Power BI determines the size of that visual by default.
 You can resize that visual to present the information it displays in the most optimal way.
 For example, if it is a small car visual, you might want to make it even smaller. Similarly, if it is a scatter
chart visual with much data, you might want to make that visual a larger size, so users can see the data
more clearly.
 To resize a visual, select the visual to display its border, then and click and drag the dark frame handles to
the size you want it to be.
 Import a custom visual
• The custom visuals that are available in Microsoft AppSource are created by Microsoft and Microsoft partners.

• Some of these custom visuals are certified and some are not.

• The certified status means that the visual meets the Microsoft Power BI team code requirements; the visual is tested to
verify that it doesn't access external services or resources and that it follows secure coding patterns and guidelines.

• NOTE: Some organizations prefer not to use custom visuals for security or other reasons.

• If you want to create your own custom visual, you can use the custom visual software development kit (SDK), which is an
open-source tool based on NodeJS (JavaScript programming language) that is available on GitHub.

• The custom visual is packaged as a single Power BI Visual Tools (.pbiviz) file that you can import into Power BI Desktop.

• Creating a custom visual is beyond the scope of this unit, so in this example, you will import a custom visual from
AppSource.
 Import a custom visual
• In the Visualizations pane, select the Get more • The new visual will appear under the other visuals in
visuals icon and then select Get more visuals. the Visualizations pane.
• On the window that displays, locate and select the visual • To add the visual to your report, select its icon.
that you want to import and then select Add. • You can then add fields to the visual and customize
its formatting, just like you would for any other
visual.
 Format and configure visualizations

• Power BI Desktop gives you a variety of options for

customizing how your selected visualizations look,
such as the colors and format of the text that they
contain.

• You should take time to explore the options to

determine what impact they each have on a visual.

• In this example, you will format and configure the

default clustered column chart visualization to better  The formatting options that are available will depend on the type of
visualization that you selected.
meet the needs of your report requirements.  Common formatting options include the Title, Background, and Border.
 In the Title section, you can add a title to the visual, if it does not have
• Start by selecting the visualization on the canvas, one, or edit the title, if it has one already.
and then select the Format button (paint roller icon)  The aim of the title is to clearly describe what data is being presented in
the visual.
to display the Format pane.  You can format the title by changing the text, text size, font, color,
background, and alignment. The subsequent section shows an example of
customizing a title.
Title
• You can edit a default title and add a title, if you don't have Background
one. • It is best practice to keep the default white background
• However, you can change the default background color to
• In this example, you will select the column chart
make a visualization more colorful and easier to read or to
visualization and then, in the Format pane, scroll down and
match a particular color scheme.
expand the Title section.
• In this example, continue with the column chart that is
• Edit the current title by adding a space
selected and then, in the Format pane, expand
between Sales and Amount, and then increase the font size
the Background section and change the color to light grey.
to 16 points.
Tooltip
• Using tooltips is a clever way of providing more contextual
information and detail to data points on a visual.

• When you add a visual, the default tooltip displays the data
point's value and category, but you can customize this
information to suit your needs.

• To expand on the data points that are displayed in the

default tooltip, you can drag a field (value) from
the Fields panel into the Tooltips bucket.

• Adding too many fields can introduce performance issues

and slow down your visuals.
 Basic Interactions
• In Power BI Desktop your report is dynamic.

• When you make a selection on one visual in the report, other visuals might change to reflect that selection.

• Similarly, if there are hierarchies in your data, you can move up and down the hierarchy to see the data at different levels.

View interactions • When you select an element in a visual, such as Components in

• When you have multiple visuals on the same report page, they all interact the Product Category visual, the other visuals update to reflect your
with each other, so you should become familiar with these interactions and selection, as illustrated in the second image.
see how your report changes.
• Compare the following two images. In the first image, the data displays at
a high level.

• When you have become familiar with the interactions, you might want to
make changes in order to control how those interactions flow between the
visuals.
 Configure conditional formatting
• Conditional formatting in Power BI Desktop allows you to specify customized cell colors, including color
gradients, based on field values, or represent cell values with data bars or KPI icons, or as active web links.
• For example, you could set up conditional formatting for your sales figures. If the sales amount falls below
zero, you could display this in red, a color that is associated with danger, so users will see this clearly and
know that they need to take action. Conversely, you could set a value for your sales target, then display
amounts over that target amount in a green color, to signify that target is met and all is going well.

• In two visuals: Table and Matrix, where it is possible to set different conditions on a column.

• In this example, you select the table visualization, then in the Format pane, expand the Conditional formatting section.

• Turn the background color on, then select the Advanced controls option. In the window that displays, set a condition to
change background color of the cell to red for cells with low values and green background to cells with high values.
The Power BI conditioning function will
automatically detect highest and the lowest
number in each column and apply
background coloring according to the
values.
• If you want to remove the conditional formatting that you set, select the Values tab on
the Visualizations pane and right-click the value (field) that you set the formatting against.
• Select Remove conditional formatting, then select the type of formatting you want to remove,
for example All or Background color.
 Design report navigation

• Report navigation is the way in which your report users move from one page in your report to the

next, move from one visual to another, and go back to where they started from.

Add navigation buttons

• To design the navigation within your report, you can create a new Navigation page in your report, and add
navigation buttons there.

• When users click on one of these buttons, they are brought directly to a different page within the report, which
you can hide, so it can be only be accessed through the Navigation page buttons.

• In this example, you create a Navigation page on which you add two navigation buttons to your other pages.
 • When you have set up the first navigation button,
copy and paste that button to create the second
navigation button, so you preserve the formatting you
applied to the first button.

• Then change the title and destination for the second

button.

• Select Blank button from the main Buttons menu,

Now when you select a button, you are brought directly to reposition and customize the button as required, then
the assigned page destination. When you are on that select Back as the action type.
destination page, to return to the Navigation page, you can
use a Back button. Here are two back navigation options:
• Select Back button from the main Buttons menu, then
reposition the button to where you want it to sit on the
page.
 Knowledge Check

Question 1: What is the benefit of using a report tooltip?

A. To give users the ability to export data from visual

B. To give additional details that is specific to the context of data that is being hovered over
C. To give users additional information about a report visual such as the author and date/time it
was created

36
 Knowledge Check

Question 2: Do you need to import custom visuals each time you want to use them
when you are developing a new report?
A. Yes, custom reports must be imported from AppSource each time you start
developing a new report unless you pin the custom visual to the visualization pane.
B. No, custom visuals are always available for selection under the visualization pane.
C. No, custom visuals only need to be imported once and will always remain in Power
BI for future use in a new report.

37
 Apply slicing, filtering, and sorting

• Power BI Desktop provides three tools that you can use to edit and configure interactions between the
visualizations you add to your report: slicers, filters and sorting.

• The process of filtering allows you to remove all of the data you do not need, so you can focus on the data
that you do need.

• You can apply filtering directly using the Filters pane, or by adding and using a slicer. Slicers and filters are
similar, they both let you filter out the unnecessary data.

• You should try out both options to see which one is the best mechanism for your report situation. You might
decide to use one option over the other, or use a combination of both.

• Contrary to filtering, the process of sorting allows you to highlight the important information without
removing any of the data.
Add a slicer
• A slicer is a type of filter that you can add to your report, so users can segment the data in the report by a specific value, such
as by year or geographical location.

• The slicer narrows the portion of the dataset that is shown in the other visualizations in the report.

• You might want to use a slicer to:

 Provide quicker access to commonly used or important filters.
 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).
 Defer queries to the data model by using a dropdown slicer, particularly when using DirectQuery.

• Slicers are not supported for input fields and drilldown functions.
Customize the filters
• The filters allow users to interact with the visuals at the report, the page, and the visual level.

• As a report designer, you can customize the Filters pane in Power BI Desktop as follows:

 Add and remove fields to filter on.

 Change the filter state.

 Format and customize the Filters pane so that it feels part of your report.

 Define whether the Filters pane is open or collapsed by default when a consumer opens the report.

 Hide the entire Filters pane or specific filters that you don't want report consumers to see.

 Control and even bookmark the visibility, open, and collapsed state of the Filters pane.

 Lock filters that you don't want consumers to edit.

• You can expand and collapse the Filters pane, so you can hide it when you do not need it. When you expand the Filters pane,
depending on the item in the report that you have selected, you will see the following sections:

 Filters on this visual - Filters that apply to the selected visual and nothing else. This section only displays if you have a
visual selected.

 Filter on this page - Filters that apply to the whole page you have currently open.

 Filter on all pages - Filters that apply to all of the pages in your report.

 Drillthrough - filters that apply to a single entity in a report

• To apply a filter, drag and drop a field from the Fields pane into the relevant section of
the Filter pane.
Sort data
• You can sort the data displayed in your visuals, so they display exactly how you want them to.

• Sorting helps you to display the most important data in the most logical way, such as in alphabetical or numeric order.

• To sort a visual, start by selecting the More options (...) button in the upper-right corner of the visual. You have three
sorting options:
 Sort descending - Sorts the visual by the selected column in the order of greatest value to smallest value.
 Sort Ascending - Sorts the visual by the selected column in the order of smallest value to greatest value.
 Sort by - Sorts the data by a specific column. Hover over this option to display the list of columns that you can
select from.
Drill through
• You can use the drill through feature to create a page in your report that focuses on a specific entity, such as a
product, category, or region.
• You can then access this page when you drill through from the related visuals that are on other pages in your
report.
• The information that displays on the drill through page will be specific to the item you select on the visual, as
illustrated in the following image:
 Enhance the report : add buttons and bookmark

Add bookmarks
• When you add a bookmark, the following elements are saved with the bookmark:
 Current page
 Filters
 Slicers, including slicer type (for example, dropdown or list) and slicer state
 Visual selection state (such as cross-highlight filters)
 Sort order
 Drill location
 Visibility of an object (by using the Selection pane)
 Focus or Spotlight modes of any visible object
 Enhance the report : add buttons and bookmark

Add buttons
• You can use buttons for many reasons, such as to switch between two visuals in a report (as required in the
previous example), to drill-down into the data in a visual or to move from one page in your report to
another.
• Power BI Desktop provides a range of types of buttons you can add to your report, as illustrated in the
following image.
 Key Performance Indicators

• Key performance indicators (KPIs) are excellent in helping you track progress toward a specific goal
over time.
• To use a KPI, you need three pieces of information:
A unit of measurement that you want to track, for instance total sales, number of employee hires,
number of loans serviced, or number of students enrolled.
A goal for the measurement so that you can compare your progress with that goal.
A time series, for instance daily, monthly, or yearly.
• Start by adding the KPI visual to the design service. The following screenshot shows the KPI icon in
the Visualizations pane.
• When configuring the KPI visual, enter the unit of measurement that you are
tracking in the Indicator prompt. Then, enter the goal under Target goals and
select the time series from the Trend axis drop-down list, as shown in the
following screenshot.

• This action will produce a KPI that looks similar to the following screenshot.

• KPIs work best in a series, for instance, showing the daily, monthly, and yearly
goals in the section of a Power BI report.
 Knowledge Check

Question 1: Which of the following filters are not available in Power BI reports?
A. Drillthrough

B. Report level
C. Page type
D. Page level

50
 Knowledge Check

Question 2: How can you analyze performance of each of your report elements?
A. By using performance analyzer
B. By analyzing your meta data
C. By deleting unnecessary rows and columns to reduce your data size

51
 Knowledge Check

Question 3: Can you use bookmarks to create a slide show in Power BI?
A. No, you cannot because bookmarks are not dynmaic.
B. Yes, you can.
C. No, you will require a specific visual to achieve this task.

52
 Summary

• Discuss various types of visual: table, matrix, bar chart, pie chart, donut chart and so on.

• Choosing the right visual is very important for report developer.

• Format the visual: background, title, color and text, etc.

• Visual should affect in performance issue of report.

• Designing the report layout using bookmarks, tooltips, button with the interaction of sorting, slicing and
drill through
 Want to learn more about Power BI visuals?

Check out these resources:

 Radial gauge charts in Power BI
 Change how visuals interact in a Power BI report
 How-To: Display 2-letter country data on a Power BI map
 Tips and tricks for color formatting in Power BI
 Tutorial: Adding formatting options to a Power BI custom visual
 Best design practices for reports and visuals
 Design effective reports in Power BI