Power BI Advanced

Agenda
• What is Story Telling with Data
• Science Behind Data Visualization
• Process Behind Data Visualization
• Custom Visuals
• Dashboards and Reports
• Polishing & Formatting
 COURSE OBJECTIVES

By the end of this course, you gain a better understanding of Storytelling with Data.
Specifically you will be able to:

• Understand the need for storytelling • Gain familiarity with Power BI report
with data layouts and structure

• Understand the agile process to • Understand implications behind

creating Power BI data visualizations choosing the right charts

• Understand the art behind • Gain familiarity over the use of Power
visualizations BI custom visuals

© 2017 Microsoft. All rights reserved.

What is Story Telling with Data
What is Story Telling with Data

Today’s world is filled with Data

© 2017 Microsoft. All rights reserved.

What is Story Telling with Data

We see reports everyday that all have the same problem

© 2017 Microsoft. All rights reserved.

What is Story Telling with Data

Are there visuals and reports that tell a great story?

For a visual to work,

they need to tell the
story the author
intended.

© 2017 Microsoft. All rights reserved.

The Science Behind Data
Visualization
Science Behind Data Visualization

Most Bad or forgettable reports all have the same problems

How many of these problems can you spot in the example?

© 2017 Microsoft. All rights reserved.
Science Behind Data Visualization

You will learn to identify and fix each of these problems!!

© 2017 Microsoft. All rights reserved.

Science Behind Data Visualization

To build great visualizations you have to understand Cognitive science

Visual memory system takes up 70% of all sensors in brain

© 2017 Microsoft. All rights reserved.

Science Behind Data Visualization

How does information pass from one part of memory to another

Iconic
memory
Short Term
memory
Long Term
Memory

© 2017 Microsoft. All rights reserved.

Iconic Memory

Iconic Memory

Iconic • Can take large volume of data

memory • Lasts only a fraction of a second

• 99% of information discarded

Short Term unless useful
memory
Long Term • Only “interesting data” is passed to
Memory Short Term Memory

© 2017 Microsoft. All rights reserved.

Iconic Memory

Spark the Iconic Memory – Mute the background noise

Things that can distract

• Bright colors all over the report

• Thick borders every where

• Grid lines

• Too much precision with numbers

• No highlights

© 2017 Microsoft. All rights reserved.

Iconic Memory
Spark the Iconic memory – Precognitive attributes

No Quantitative Perception = Orientation, Shape, Enclosure, Added Marks

© 2017 Microsoft. All rights reserved.

Short Term Memory

Short Term Memory

Iconic • Lasts only a few seconds

• Can take in 3 to 5 pieces of info at

memory most

• Memory tries to form these 3-5

Short Term pieces using patterns it detects in
memory data
Long Term
Memory • If these pieces of info do not have a
story the data will not pass to Long
© 2017 Microsoft. All rights reserved.
Term Memory
Short Term Memory
Spark the Short Term Memory – Gestalt’s principles
Proximity
Similarity
Figure & Ground
Continuity
Enclosure
Symmetry
Symmetry
Long Term Memory

Long Term Memory

Iconic
memory • Lasts for a Long Time

• Data needs to be in Long Term Memory to

induce decision making
Short Term
memory
Long Term
Memory

© 2017 Microsoft. All rights reserved.

Long Term Memory

Sparking Long Term Memory

© 2017 Microsoft. All rights reserved.

The Process Behind Data
Visualization
Data Structure & Data Grain
Learn about your
audience needs

Rapid
Prototype
Who? Where?
What? How Many?
When? Why? User Feedback

Build visuals in tool

User Feedback

© 2017 Microsoft. All rights reserved.

Convert the Story to a Data Model
List your big questions:
1. What is my Total Sales for a Selected Year and Region?

2. How is my Total Sales doing Year Over Year?

3. How are my Units trending for various States in my region?

4. How is my Sales doing by Channel, Device, Category for selected Year?

5. Which categories are performing best to worst by Total Sales ?

Units By Time (Year, Month)

These are Measures These are
Total Sales By Geography (Region, State or City)
which live in Fact Attributes that live
Gross Profit tables By Campaign (Channel or Device) in Dimension tables

© 2017 Microsoft. All rights reserved.

Convert Story to Data Model

Dim Fact Dim Dim

Relationship Relationship

© 2017 Microsoft. All rights reserved.

Chart Selection
 Choosing the Right Charts for the Right Purpose

Stacked Bar Tree Line Scatter Plot KPI Map Area

Clustered Bar Pie Funnel Bubble Gauge Filled Map Bubble

Clustered Column Stacked Area Line and Stacked Column Map Multi-row Card

Table 100% Stacked Column Stacked Column Card

Matrix Water Fall

© 2017 Microsoft. All rights reserved.
Column charts for Comparison

© 2017 Microsoft. All rights reserved.

Column chart vs Bar Chart for Comparison

© 2017 Microsoft. All rights reserved.

Stacked vs Clustered Column (Bar) Chart Types

© 2017 Microsoft. All rights reserved.

Line Charts

© 2017 Microsoft. All rights reserved.

Area Chart vs Line Chart

© 2017 Microsoft. All rights reserved.

Stacked Area Charts

© 2017 Microsoft. All rights reserved.

One Use Case for Area Charts

© 2017 Microsoft. All rights reserved.

Constructing Effective Tree Maps

© 2017 Microsoft. All rights reserved.

Constructing Effective Pie charts

© 2017 Microsoft. All rights reserved.

Constructing Effective Tables

© 2017 Microsoft. All rights reserved.

Constructing Effective Cards

© 2017 Microsoft. All rights reserved.

Constructing Effective Slicers

© 2017 Microsoft. All rights reserved.

Constructing Scatter Charts

© 2017 Microsoft. All rights reserved.

Custom Visuals
Power BI Customizations

Import Custom Visual

© 2017 Microsoft. All rights reserved.

Custom Slicers

© 2017 Microsoft. All rights reserved.

Power BI Customizations

Create Custom Visual

© 2017 Microsoft. All rights reserved.

Dashboards and Reports
Terminology & Definitions

A Power BI dashboard is a single page, often called

a canvas, that uses visualizations to tell a story.
Because it is limited to one page, a well-designed
dashboard contains only the most-important elements
of that story.
A Power BI report is a multi-perspective view into
a dataset, with visualizations that represent
different findings and insights from that dataset.
A report can have a single visualization or pages
full of visualizations.
Workspaces are containers for
dashboards, reports, workbooks, and
datasets in Power BI. There are two
types of workspaces: *My workspace and
app (group) workspaces.

© 2017 Microsoft. All rights reserved.

Data Sets, Reports, Dashboards (in Service)
Executive
Dashboard

No Dashboard
Report is directly
Summary Project Orion Project Draco Project Pisces
shared with users
Dashboard Dashboard Dashboard Dashboard

Subscription Web Report Audit Report Telemetry Project Orion Project Project
Report Report Report Draco Report Pisces
Report

Example 1 Example 2 Example 3

© 2017 Microsoft. All rights reserved.

Layouts: Dashboard vs Report

© 2017 Microsoft. All rights reserved.

Dashboard to Report Transitions

Dashboard Tile (single KPI) Action Oriented – click thru to report

Report – tell Top level story of KPI

* Drill to another Report (Sliced to Specific details)

In Report can use “Back” to navigate back up

© 2017 Microsoft. All rights reserved.

Pin a Live Report Page as a Dashboard

© 2017 Microsoft. All rights reserved.

Pin a Live Report Page as a Dashboard Tile

© 2017 Microsoft. All rights reserved.

Dashboards
- Layout & Structure
Layouts

© 2017 Microsoft. All rights reserved.

Dashboard Layouts – Column Layout

1st KPI 1st KPI 1st KPI

Area 1 Area 2 Area 3

2nd KPI 2nd KPI 2nd KPI

Area 1 Area 2 Area 3

Chart Chart Chart

Area 1 Area 2 Area 3

© 2017 Microsoft. All rights reserved.

Dashboard Layouts – Vertical Matrix Layout

© 2017 Microsoft. All rights reserved.

Dashboard Layouts – Horizontal Matrix

https://powerbi.microsoft.com/en-us/blog/how-microsoft-uses-power-bi-to-run-its-growing-cloud-business/
© 2017 Microsoft. All rights reserved.
Reports
- Layout & Structure
Use Storyboarding to Design Report Layout

Logo Subject

When?
Who? Where?

What?

© 2017 Microsoft. All rights reserved.

 Storyboarding Tools

Install the Storyboarding Toolbar: https://docs.microsoft.com/en-us/vsts/work/backlogs/office/storyboard-your-ideas-using-powerpoint

© 2017 Microsoft. All rights reserved.
Polishing and Formatting
Colors
How do you pick a color scheme?

• Check with your Marketing team for Color/Style Guides

• Use Cynthia Brewer’s Color Brewer or other sites to choose Color Blind friendly colors
• Use the Adobe Color Wheel to get custom complementary colors
• Paletton.com is the color scheme generator that the Power BI product team uses
• Color Blind Sensitive: Contrast Analyzer is a tool you can use that creates a “lens” to see
how people with visual disabilities would view your reports:
https://developer.paciellogroup.com/resources/contrastanalyser/

• Use Power BI’s default color scheme

Pixie
© 2017 Microsoft. All rights reserved.
Types of Colors

© 2017 Microsoft. All rights reserved.

Things to remember when using colors

• Do not have more than 3-5 colors display on a single visual (think of Short Term memory)

• The eye cannot differentiate more than 5 colors of same hue

• Try to have a semantic meaning for your colors (Red = Bad, Green = Good)

© 2017 Microsoft. All rights reserved.

Font

2 Font Types

Serif Sans-Serif Monospace Informal

Serif Sans-Serif Monospace Informal

• Sans-Serif fonts most suited for Digital Media in a Professional Setting

• Ex. Segoe, Calibri, Trebuchet

© 2017 Microsoft. All rights reserved.

Changing font weight using three techniques
• Change font size
Hi Power BI - Segoe UI Light 28
Hi Power BI - Segoe UI Light 24

• Choose different font with greater weight from same family

Hi Power BI - Segoe UI (Body) 24
Hi Power BI - Segoe UI Light 24

• Bold an existing font

Hi Power BI - Segoe UI Light 24 (Bolded)
Hi Power BI - Segoe UI Light 24 (Non-Bolded)

Try all three techniques to see which one looks the best

© 2017 Microsoft. All rights reserved.

Font Recommendations

• Choose at the most 2-3 font types/sizes on a report page/dashboard

• Choose a lighter weight font Ex. “Segoe UI Light” for

o Axis
o Non important Data Labels
o Text box
o Non Titles

• Use a higher weight font from same family for Titles rather than (bold) Ex. Segoe UI Bold

© 2017 Microsoft. All rights reserved.

Use of Fonts

© 2017 Microsoft. All rights reserved.

Icons

• Effective use of icons can add to perceptual depth of a report

• Don’t over use icons or misuse icons
© 2017 Microsoft. All rights reserved.
Dashboard Tile Art

• Rectangle image size 1.81” by 2.66”

• Add header text
Month to Date
• Add subtitle text
MTD Actual
vs MTD Goal
• Add circle shape for icon emphasis
• Add icon from native Office 2016 Icon library
• Change color of icon graphic
• Snip out the box and save as PNG

© 2017 Microsoft. All rights reserved.

Proportional Sizes for Custom Dashboard Tiles

• 1 x 1 Tile – 266:181 pixels

• 2 x 1 Tile – 540:181 pixels
Rule of Thumb Formula: • 3 x 1 Tile – 816:181 pixels

• Images Width = 266 * (Number of Tiles Wide)+ 8 (Number of Tiles Wide – 1)

• Image Height = 181 * (Number of Tiles High)+ 8 (Number of Tiles High– 1)

© 2017 Microsoft. All rights reserved.

Symbols

⇙▼ ⚠ ➕ ✔ ⚐ ★ ◕
Different Ways to Use Symbols

• Unicode Symbols - You can find Unicode symbols at Unicode-table.com or

Unicode.org (need to convert hex to dec)

(rendered in Black/White)
• Locate the web page for the desired symbol
• Scroll down to see the “Your Browser” section
• Highlight the symbol and copy ( Ctrl+C )
• Paste ( Ctrl+V ) into the formula bar of your DAX
© 2017 Microsoft. All rights reserved.
KPIs

Different Ways to Obtain/Use KPIs

• Use the KPI visualization in Power BI Desktop

• If you import an Excel data model with KPIs into Power BI Desktop, the KPIs will be
maintained and render in color
• If you connect Power BI Desktop to either SSAS Multi-Dimensional or Tabular Model
which contains KPIs, the KPIs will render in color in your visualizations
• In the Table Visual, use conditional formatting to set colors in a column then set the
column width very small to only display the colors

© 2017 Microsoft. All rights reserved.

Report Layout “Hacks”

© 2017 Microsoft. All rights reserved.

Bar charts as KPIs

© 2017 Microsoft. All rights reserved.

Spark Lines

© 2017 Microsoft. All rights reserved.

Hyperlinks and Images

• Full path of the URL in a field

• Modify the Data Category (modeling ribbon)

• ImageURL – to see image
• WebURL – to see Link Symbol

• Supports all link types displayed at left.

© 2017 Microsoft. All rights reserved.

Final Thoughts
 Power BI Support Resources
Contact Support
Report Errors, Issues – Support.PowerBI.com
Resources
 Community.PowerBI.com – Community Forum
 Report Theme Gallery – A showcase for stunning report themes
 Data Stories Gallery – Get inspired with Data Stories by other Power BI users
 R-Visuals Gallery – Get inspired by others use of R for analyzing their data
 Store.office.com – Custom PBI visuals and R visuals you can download and use in your story

 Power BI Blog - weekly updates

 User Voice for Power BI – Vote on (or submit) your favorite new ideas for Power BI
 Issues.PowerBI.Com – log issues with the community
 Whitepaper - Creating an Enterprise Class Dashboard Solution with Power BI
 Guided Learning Self Service Power BI training

 DAX Formula Language – syntax for DAX

 DAX Patterns – Great website to learn new patterns for the DAX Language
 Power Query Formula Language – syntax for the “Query” language
 Paletton.com – a color scheme generator
 https://unicode-table.com/en/ – Unicode Character Table
 Theme Generator
 Contrast Analyzer: a tool that creates a “lens” to show how people with different visual disabilities might see your reports

Instructors: