This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here                     

PowerPivot Workshop

18th October 2012 - 19th October 2012

Who is it for?

This course is aimed at Excel and BI professionals who want to develop models in PowerPivot with Excel 2010.

What you will learn

The workshop outline, in detail, is as follows:   

What is PowerPivot? 
What is a PivotTable? 
PowerPivot setup
Basic PowerPivot Concepts 
Formatting Numbers 
Understanding Calculated Columns 
Using Lookup Tables 
Understanding Measures 
Handling Many Tables 
Refreshing Data 
Using Slicers    

Introduction to DAX
DAX Data Types 
DAX Operators 
Calculated Column Examples 
Measure Examples 
Introduction to the Execution Context (Row and Filter) 
Choosing Between Calculated Columns and Measures 
Handling Errors in DAX 
Common DAX Function Examples    

Understanding Data Models
What is a Data Model? 
Why PowerPivot Users need Data Modelling? 
Difference Between Classical and PowerPivot PivotTables 
Physical and Logical Data Models 
Normalization and Denormalization 
Empty and Default Values 
The SQL Query Designer as a Data Modelling Tool 
Different kinds of Joins 
Setting Relationships Manually    

Loading Data and Models
Understanding Data Connections 
Using Existing Connections 
Loading Tables from SQL Server 
Filtering Options 
Loading and Detecting Relationships 
Loading from Views 
Loading from Access 
Loading Data from Analysis Services 
Using Linked Tables 
Loading from Excel Workbooks 
Loading from Text Files 
Using Copy & Paste Operations 
Loading From OData Feeds 
Loading From SharePoint    

Understanding Evaluation Context
What is Row Context? 
What is Filter Context? 
The EARLIER and EARLIEST Functions    

Introduction to the Calculate function 
Examples of how to use the Calculate function 
Detailed Analysis of Calculate Behaviour    

The Calendar Table in PowerPivot
Why a Calendar Table is Useful 
Creating a Calendar Table with Excel 
Common Calendar Calculations: Year To Date, Same Period Last Year, Previous Year Growth 
Semi Additive Measures    

Mastering Pivot Tables and Excel Cube Formulas
Flattened Pivot Tables 
Using Custom Sorting and Filtering 
Computing Ratios and Percentages 
Defining Sets 
Using Excel Cube Formulas    

Advanced DAX Patterns
Advanced Ratio and Percentage 
Computing Standard Deviation 
Ranking Over a Measure 
ABC and Pareto Analysis 
Events in Progress Pattern
Advanced Data Model Patterns 
Banding  DAX to resolve Complex Relationships 
Using Many To Many Relationships    

SharePoint Integration
PowerPivot for SharePoint 
PowerPivot Gallery 
Publishing an Excel Workbook 
Parameters Pane 
PowerPivot Data Refresh 
Data Connections 
User Credentials 

Key benefits

This course will enable you to build your own PowerPivot models, create calculations using DAX and build a complete reporting solution using Excel 2010 and Sharepoint 2010.

This course is taught under license from SQLBI (

Cost: £499 + VAT until August 31, 549 + VAT after that +VAT


Chris Webb

Chris Webb is the owner of Crossjoin Consulting, and is a consultant specialising in Analysis Services, MDX, PowerPivot and DAX. He is a co-author of "Expert Cube Development with SQL Server 2008 Analysis Services",  "MDX Solutions with Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase" and "SQL Server Analysis Services 2012: The BISM Tabular Model" and blogs at

Bob Phillips

Bob Phillips is an Excel MVP and a well-known figure in the UK Excel community.



LMG Training and Development
15 Risborough Street

legal disclaimer

Cancellations must be submitted in writing, either via email or by post. Registrants whose cancellations are received at least 2 weeks before the beginning of the course are entitled to a full refund minus a £80 processing fee plus any credit card charges incurred. No refunds will be given to registrants who cancel less than 2 weeks before the beginning of the course or who fail to attend.

In the extraordinary case where the course is cancelled a full refund will be given to all paid registrants. Crossjoin Consulting will not refund any other amount paid by registrants to other companies, including travel expenses and hotel reservations.

decoration decoration decoration decoration