Book a course

Duration: 1-day

Location: inhouse, online or at our UK locations

What you will learn

This Power Pivot Intermediate course is designed to give users an understanding of the more complex features within Power Pivot including, advanced calculated columns and a detailed look at DAX measures.

It goes beyond reporting with Power Pivot by also including Power Query, used to seamlessly collect data from different sources, clean it, then export to Power Pivot. It also looks at Microsoft Power BI Desktop where you can seamlessly run your reports online.

We will look deeper into relationships to get greater analysis from your data and how to merge disjointed sets of data into unified reports. You will learn how to make certain columns in a Pivot Table behave as if filtered, while other columns in the same table do not (seeing this helps better understand it).

We look at creating time-intelligent calculations such as “Year over Year” and "Year to Date" analysis, and how formulas can be reused across reports of completely different shapes.

Prerequisite

Attendees are required to be familiar with the Power Pivot Introduction content before attending this course.

Course outline

Disconnected tables

  • A Parameterised Report
  • Adding the Parameter Table
  • Disconnected Table Variation

Introducing the FILTER Function

  • When to Use FILTER
  • Why is FILTER Necessary?
  • Applying FILTER in the Thresholds Example
  • More Variations on Disconnected Tables

ALL, The Remove a Filter Function

  • The Practical Basics
  • Negating a Slicer
  • Variations of ALL
  • ALLEXCEPT and ALLSELECTED Measures

IF, BLANK and Other Conditional Functions

  • Using IF in Measures
  • The BLANK Function
  • The DIVIDE Function
  • The ISBLANK Function
  • HASONEVALUE Function
  • IF Based on Row/Column/Filter Tools
  • The VALUES Function
  • The SWITCH Function

SUMX and Other X “Iterator” Functions

  • SUMX in Action
  • MINX, MAXX and AVERAGEX Functions
  • The FILTER Function
  • The COUNTX and COUNTAX Functions, Different From COUNTROWS
  • Using the X Functions on Fields that Aren't Displayed
  • RANKX Function and it's Parameters
  • The Use of ALL
  • The TOPN Function
  • Non-Measure Second Arguments to the X Functions

Multiple Data Tables and their Relationship

  • Service Calls and Sales Together
  • Why We Do Not Flatten Tables into Just One
  • Running Calculations Across Tables
  • Using Fields from Lookup Table vs. Data Table
  • Data Tables Connected to Some, Not All Lookup Tables
  • Non-Measure Second Arguments to the X Functions

Multiple Data Tables, Differing Granularity

  • Budget verses Actuals
  • Achieve Everything with Relationships
  • Needing New Lookup Tables
  • Using the RANKX Argument
  • The Amazing "Cross-Rank" Measure

Performance: Keep Things Running Fast

  • The Importance of Speed
  • Slicers: The Biggest Culprit
  • The Effects of Cross-Filtering
  • Slicers For Which You Should Turn Cross-Filtering Off
  • The Shape of Your Source Table Is Important
  • Narrower Tables are Better
  • Imported Columns Generally Better than Calculated Columns
  • "Star Schema" Generally Better than "Snow Flake Schema"
  • Measure Performance

Power Query to the Rescue

  • Brining Order to Messy Data
  • Appending Files to Create a Single Power Pivot Table
  • Connecting to CSV Files
  • Loading Data into Power Pivot
  • Using Append to Combine Tables
  • Refresh Appended Tables
  • Combine Multiple Files into a Table
  • Change Data Types and Remove Errors
  • Adding Custom Columns to Lookup Tables
  • Using Power Query to "Unpivot" a Table
  • Using Power Query to Create a Lookup Table from a Table
  • Creating a Calendar Table: Advanced Power Query
  • How NOT to Use Power Query

Power BI Desktop - Microsoft Business Intelligence

  • The New Kid On the Block
  • Creating Reports
  • Same Engines, Different Visuals
  • Importing Power Pivot Data Models
  • Sharing Power BI Desktop Files

“Complicated” Relationships

  • Multiple Relationships between the Same Tables
  • The USERRELATIONSHIP Function
  • Many to Many Relationships

Row and Filter Context Demystified

  • Gateway to Doubling Your Superpowers
  • Interaction with Relationships

CALCULATE and FILTER More Nuances

  • CALCULATE Filter Arguments Override Pivot Filters
  • The Second Purpose of ALL, FILTER, Etc.
  • Nesting Functions Inside One Another
  • Putting it All Together

Time Intelligence with Custom Calendars

  • Connecting the Periods Table
  • Using CALCULATE, FILTER and ALL in Custom Calendars
  • Year Over Year Custom Calendar Measure
  • Percent Growth Formulas

Advanced Calculated Columns

  • Grouping Columns
  • Summing up in a Lookup Table
  • Use of the EARLIER Function