Book a course

Duration: 1-day

Location: inhouse, online or at our UK locations

What you will learn

This two-part course starts with an in-depth look at Pivot Tables, after all, that's where data from Power Pivot ends up when running reports. Then progresses to an introduction to Power Pivot.

The first part looks at creating and managing Pivot Tables, working with its layouts, value formats and running various calculation analysis. You will be creating Pivot Charts and Slicers to create a user-friendly dashboard.

The second part focuses on the essential tools within Power Pivot, loading data into Power Pivot from various sources, querying and extracting data, running Calculated Columns, and includes an introduction to the powerful DAX language (Data Analysis Expressions).

At the end of this course you will have the skills to run reports in Pivot Tables, then analyse data with Power Pivot's Data Model. You will understand the versatility of table relationships, and create a dashboard to confidently produce professional reports.

Prerequisite

Attendees are required to be Excel users before joining this Pivot Table and Power Pivot course.

Course outline

Pivot Table Reports

  • Analysing table data with PivotTable reports
  • Managing a PivotTable report's layout
  • Creating statistical reports
  • Controlling number formats
  • Creating a PivotChart
  • Filtering with Slicers

Advanced PivotTable Tools

  • Group numeric and date fields
  • Produce percentage and difference from analysis
  • Write Field and Item calculations
  • Create a Pivot Table from multiple tables

PivotTables and Data Models

  • What is a Data Model?
  • Create a Pivot Table from a Data Model
  • Creating Table Relationships
  • The 'Lookup' Table
  • Using Related Tables in a Pivot
  • Viewing an Excel Data Model
  • Difference between Data View and Diagram View

Introduction to Power Pivot

  • A Revolution Built On You
  • Overview of Power Pivot and 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
  • Learning Power Pivot the Excel Way
  • Many Different Sources
  • Linked Tables
  • Pasting Data Into Power Pivot
  • Importing From Text Files
  • Database Source Types
  • 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