Course Code: bspkpbi
Duration: 21 hours
Course Outline:

Day 1

Overview of Power BI

  • The value offering
  • Licensing and Pricing
  • Market Positioning

Power BI Cloud Service (the wow features)

  • Overview of the Cloud Service
  • Importing a Report into the Cloud Service
  • Building a Dashboard
  • Dashboard Flow and Navigation
  • Power BI for Mobile
  • Q&A (Natural Language Queries)
  • Quick Insights
  • Content Packs
  • Demo Samples
  • Publish to web (embedded)
  • Export to PowerPoint

Power BI Desktop

  • Overview of Power BI Desktop
  • Similarities to Excel Power Pivot
  • Similarities to Excel Power Query
  • Technical Options & Settings
  • Monthly Updates

Importing and Transforming Data

  • Importing Data from various sources
  • Using Direct Query
  • Query Editor Overview
  • Understanding Query Design &the ETL Process
  • Query Building Best Practice
  • Understanding Applied Steps
  • Data Transformations
    • Cleaning data (covers many different methods)
    • Merging Datasets
    • Unpivoting Data
    • Combining Files from a folder
    • Appending datasets
  • Using a Dynamic Fiscal Calendar Table
  • Creating Parameters

Data Modelling

  • Creating Relationships
  • Bidirectional Relationships
  • Default Summarization
  • Custom sorting of columns
  • Data Types & Formatting
  • Creating data synonyms


Report Building

Build a Time Intelligence Report

Day 2

Report Building (continued)

  • Report Layout Guide lines
  • Creating Calculated Measures
  • Understanding DAX syntax
  • Implicit Vs Explicit Measures
  • Base Measures & Metrics
  • Commonly Used DAX Functions
    • Sum, Average
    • Time Intelligence (MTD, QTD, YTD, Prior Year)
    • Calculate and filter context
    • Filter, All
    • IF, HasOneValue
    • Switch
    • Divide
    • UserRelationship
    • Selected Value
  • Optimizing Measures for Self-Service BI
  • Using Quick Measures

Build a Monthly Trend Report

  • How to Slice by Measure
  • Dynamic Headings & Dates
  • Types of Slicers and filters
  • Understanding KPI cards
  • Using Field, Format, Analytics Pane

Build a Tabular Report (Matrix)

Report Functionality     

  • Drill-Downs
  • Drill-Through
  • Slice by Relative Dates
  • Focus Mode
  • Setting Visual Interactivity
  • Exporting Data from Power BI Desktop
  • Multiple relationships with the fact table

Build a report based on Shipped Date

Report Visualisations

  • Choosing visuals based on context
  • Native & Custom Visuals
  • Using Bullet, Scatter, Ribbon & Waterfall charts

Build a What-if Analysis Report (time permitting)

Optimizing the Design

  • Adding Tool Tips
  • Types of Conditional Formatting
  • Custom Page Sizing
  • Logo & Icons
  • Shapes and Borders
  • Formatting Tips & Tricks
  • Power BI Bookmarking (time permitting)

Best Practice Guidelines and Development Tips

Governance & Distribution

  • Row Level Security – Creating and Testing Roles
  • Publishing to the cloud Service
  • Security – Add Users to Defined Roles
  • Sharing Dashboards & Reports
  • Creating App Workspaces & Publishing Apps

Day 3

Advanced Data Modelling for Financial Analysis

  • Ensuring Base GL Grouping
  • Custom Account Mapping& Grouping
  • Data Validation & Report Reconciliation
  • Managing Mapping of any new GL Accounts
  • Handling Custom Subtotalling
  • Creating lines for Gross Profit & Net Profit etc.
  • Adding Row Spacing in a Matrix
  • Custom Sorting of Reporting Lines


Creating Custom Reporting Layouts

  • DAX Measures for Filtering by Account Types
  • DAX Measures to Support Custom Subtotalling
  • Custom Layouts using a Matrix
  • Designing KPI cards
  • Matrix Design
  • Adding additional Dimensions (Departments etc.)
  • DAX Measures for Finance


Financial Ratios

  • DAX Requirements
  • Design & Layout options