Book a course

Duration: 1-day

Location: inhouse, online or at our UK locations

What you will learn

This course focuses entirely on Power Pivot, looking at the essential tools to turn your data into polished reports. It's designed to give you a solid understanding of the fundamentals of Power Pivot usage.

It looks at loading data into Power Pivot from various sources, creating table relationships to run powerful real-world reports from multiple tables.

You will perform data analysis by generating totals through Calculated Columns, then pulling those calculations into an Excel Pivot Table, incorporating a user-friendly dashboard.

It also concentrates on the DAX language (Data Analysis Expressions), the core capability of Power Pivot. This might sound complicated but if you are already familiar with some of Excel's formulas, it's a seamless progression.

Prerequisite

Course attendees are required to be regular users of Excel and familiar with Pivot Tables before attending this Power Pivot Introduction course.

Course outline

Introduction to Power Pivot

  • Differences in Power Pivot and the Power BI Desktop
  • Learning Power Pivot the Excel Way
  • When to use Power Pivot

Loading Data Into Power Pivot

  • Launching the Power Pivot Window
  • Saving Power Pivot data
  • Many Different Sources
  • Linked Tables
  • Pasting Data Into Power Pivot
  • Importing From Text Files
  • Database Source Types
  • SharePoint and Cloud Sources
  • Other Important Features
  • Managing Existing Connections

Introduction to Calculated Columns

  • Adding Your First Calculated Column
  • Referencing a Column
  • Properties of Calculated Columns
  • Traditional Excel Functions Available

Introduction to DAX Measures

  • What is DAX (Data Analysis Expressions)?
  • Adding Your First Measure, SUM
  • Naming a Measure
  • Viewing Measures in a Pivot
  • Referencing Measures in Other Measures
  • Traditional Excel Functions Available
  • Editing Existing Measures
  • Define DAX Number Formatting
  • COUNTROWS and DISTINCTCOUNT
  • Using Pivot Slices to Filter

The Rules of DAX Measures

  • How Does DAX Get Those Numbers?
  • Running Filters in Data Tables
  • Detecting Filter Content
  • Referencing Columns and Measures Differently

CALCULATE, New Favourite Function

  • A Supercharged SUMIFS
  • The CALCULATE Syntax and how it Works
  • Useful Examples of CALCULATE
  • Evaluation of Multiple Filters

Thinking in Multiple Tables

  • Creating Table Relationships
  • The 'Lookup' Table
  • The Diagram View
  • Using Related Tables in a Pivot
  • How Filters are Applied
  • How CALCULATE Flows Across Relationships

Introduction to Time Intelligence

  • Standard Calendar Verses Custom Calendar
  • Calendar: A Special Lookup Table
  • Enable Date Filtering
  • Time Intelligence Functions
  • DATESMYD DATESYTD FIRSTDATE LASTDATE

KPI (Key Performance Indicators)

  • How KPI's work
  • Creating a KPI
  • Defining status thresholds
  • Adding a KPI to a Pivot Table

Power Pivot in Power BI Desktop

  • Connecting to data sources
  • Adding a Calculated Column
  • Adding a Measure
  • Create and Optimize Data Models
  • Publishing a report to Power BI Pro