98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsExcel PowerPivot Training Courses
The business intelligence tool for Excel
Face to face / Virtual public schedule & onsite training. Restaurant lunch included at STL venues.
Designed for Excel 365
From £495 List price £650
- 1 day Instructor-led
- Courses never cancelled
- Restaurant lunch
PowerPivot provides the real power to crunch & analyse data on a scale previously unimaginable with pivot tables.
Now it is possible to process millions of rows of data in Excel enabling deeper business insight and shorter decision making cycles.
Syllabus
Who is this course for?
This course is suitable for any existing Excel user who has to manipulate, analyse and report on massive amounts of data. Types of PowerPivot Course London attendees have varied from business analysts, commercial directors, finance teams, client service/revenue managers to the Office for National Statistics.
Prerequisites
A very good knowledge of Excel, including a working knowledge of PivotTables and an understanding of nested functions.
Benefits
- At the end of this 1 day course delegates will have the skills required to start using PowerPivot.
- The course will show delegates how to leverage their existing PivotTable knowledge in Excel with PowerPivot.
- Enable delegates to better understand how PowerPivot might be applied back in their workplace.
Course Syllabus
Getting started with PowerPivot
Start Excel & launch the PowerPivot window
Navigating the PowerPivot window
Exploring the PowerPivot tab and field list in Excel
Adding data to PowerPivot
Data sources and types supported in PowerPivot workbooks
Importing data (from pre-prepared file)
Add Data by using Excel Linked Tables
Using Power Query
Preparing data for analysis
Working with Tables and Columns
Filtering and Sorting Data
Creating Relationships Between Tables
Creating and Working with calculations
Data; formatting and layout
Creating, deleting a table
Rename a Table or Column
Set the Data Type of a Column
Hide or Freeze Columns
Undo or Redo an Action
Sorting and filtering data in a table
PowerPivot and relationships
Understanding Relationships
Create a Relationship Between Two Tables
View and Edit Relationships
Delete Relationships
Troubleshoot Relationships
Calculations in PowerPivot
Overview of Data Analysis Expressions (DAX) language
Building Formulas for Calculated Columns and Measures
Understanding the Use of Relationships and Lookups in Formulas
Understanding Aggregations in Formulas
Filtering Data in Formulas
Recalculating Formulas
Creating PivotTables, Charts & Reports
Create a PivotTable or PivotChart Report
Create a Reporting Services Report with PowerPivot Data
Create a Measure in a PivotTable or PivotChart
Create and Change the Field Layout in a PivotTable or PivotChart Report
Delete a PivotTable or PivotChart Report
Filter Data using Slicers
Work with Relationships in PivotTables
Change the Display Language
Prices & Dates
What you get
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Virtual training
Regular breaks throughout the day.
Learning tools
In-course handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
|
Testimonials
Barts Health NHS Trust
David Bays,
Cardiac Audit Data Analyst
This is the second course I've been to run by STL and didn't disappoint. The trainer Jens really knows his stuff.
Excel PowerPivot
Logicor Europe Limited
Anay Kamitkar,
-
Consider each participant having 2 screens - one for their own use and one that replicates the instructor's display
Excel PowerPivot
Telent
Paul J Edwards,
Business Systems Manager
Excellent course, Yen's is a proper old school geek (no offence) who won me over with his enthusiasm for the subject.
Excel PowerPivot
We offer public schedule courses for training power bi London. Courses never cancelled, include a restaurant lunch and 2 years post training support and easy same day rescheduling.
Training manual sample
Below are some extracts from our Excel training manuals.
Unit 11 – A selection of DAX Functions
In this unit, you will learn how to:
Understand the DAX function CALCULATE()
Understand the DAX function ALL()
Understand the DAX function ALLEXCEPT()
Understand the DAX function RELATED()
Understand the DAX function SUMX()
Understand the DAX function COUNTX()
Understand the DAX function COUNTAX()
CALCULATE()
Have you ever used the Excel function SUMIF(), or perhaps its newer cousin, SUMIFS()?
I describe CALCULATE() as “the SUMIF/SUMIFS you always wish you’d had.” You are going to love this function, because it works wonders.
In case you are one of the pivot pros who managed to skip SUMIF() and SUMIFS() in normal Excel, they are both very useful functions: they sum up a column you specify, but filter out rows that don’t fit the filter criteria you specify in the formula. So, for instance, you can use SUMIF to sum up a column of Sales figures, but only for rows in the table where the Year column contains 2012.
Does that sound familiar? It sounds a lot like the Golden Rules from the prior chapter – “filter, then arithmetic.” An interesting similarity, and CALCULATE() continues in that same tradition.
Anyway, CALCULATE() is superior to SUMIF() and SUMIFS() in three fundamental ways:
It has cleaner syntax. This is the smallest of the three advantages, but it feels good.
It is an “anything” IF, and not limited to SUM/COUNT/AVERAGE. There is no MAXIF() function in Excel for instance. It is literally unlimited – it allows you to take any aggregation function (or even a complex multi-function expression!) and quickly produce an IF version of it.
It can be used in pivots (as part of a measure), which normal SUMIF() cannot.
CALCULATE(
Ex: CALCULATE(SUM(Sales[Margin]), Sales[Year]=2001)
Ex: CALCULATE([Sales per Day], Sales[Year]=2002, Sales[ProductKey]=313)
Start with a simple pivot. Year on rows, [Total Sales] measure on values:
OK, let’s add a new measure, one that is always filtered to Year=2002:
CALCULATE([Total Sales], Sales[Year]=2002)
The results are shown below;
Do those results surprise you? I bet they are close to what you expected, but maybe not exactly. You might have expected years 2001 and 2003 to display zeroes for our new measure, and you might be scratching your head a bit about the grand total cell, but otherwise, having the new measure always return the 2002 value from the original measure is probably pretty instinctive.
It’s not very often that I write a CALCULATE measure that filters against a column that is also on the pivot (Sales[Year] in this case). That seldom makes any real-world sense. I just started out like this so you can see that the $6,530,344 number matches up.
So, to make this a bit more realistic, let’s take Year off of the pivot and put MonthNum on there instead:
This probably makes even more sense than the prior pivot. The grand total is still that $6.5M number, but every other cell returns a distinct number – the sales from 2002 matching the MonthNum from the pivot.
How CALCULATE() Works
Now that we’ve looked at a couple of examples, let’s examine how CALCULATE() truly works, because that will clear up the handful of somewhat unexpected results in that first example.
There are three key points to know about CALCULATE(), specifically about the
The
ter> arguments operate during the “filter” phase of measure calculation. They modify the filter context provided by the pivot – this happens before the filters are applied to the source tables, and therefore also before the arithmetic phase.
If a
ter> argument acts on a column that IS already on the pivot, it will override the pivot context for that column. So, in our first example above, the pivot is “saying” that Sales[Year]=2001, but I have Sales[Year]=2002 in my CALCULATE(), so the pivot’s “opinion” of 2001 is completely overridden by CALCULATE(), and becomes 2002. That is why even the 2001 and 2003 cells (and the grand total cell) in the first example returned the 2002 sales number.
If a
ter> argument acts on a column that is NOT already on the pivot, that ter> will purely add to the filter context. In our second example, where we had Sales[MonthNum] on the pivot but not Sales[Year], the Sales[Year]=2002 filter was applied on top of the Month context coming in from the pivot, and so we received the intersection – 2002 sales for month 1, 2002 sales for month 2, etc.
Examples of CALCULATE()
The [2002 Sales] measure that I have been using as an example so far is a good way to show you how CALCULATE() works, but it might not seem terribly useful. So, let me show you two quick examples that are much more broadly applicable.
Transactions of a Certain Type
Here is one that I see all the time in the retail sales business: not all transactions are normal sales. Some businesses record many different transaction types including “Normal Transaction,” “Refund,” and “Promotional Sales Transaction.”
My database has a column for that, so I went ahead and imported it into my Sales table (using Table Properties). Here, we see that it has three values:
I now want to write four new measures, defined here in English:
“Regular” Sales – Just transactions of type 1
“Promotional” Sales – Just transaction of type 3
“Refunds” – transactions of type 2, expressed as a negative number
“Net Sales” – Regular plus Promotional sales, less Refunds
Now, here are the formulas for each:
[Regular Sales] =CALCULATE([Total Sales], Sales[TransType]=1)
[Promo Sales] =CALCULATE([Total Sales], Sales[TransType]=3)
[Refunds] =CALCULATE([Total Sales], Sales[TransType]=2) * -1
[Net Sales] =[Regular Sales] + [Promotional Sales] + [Refunds]
The results are displayed below:
Let us continue down the Practical Road, let’s see what percentage of our sales are due to us running promotional campaigns:
[Pct Sales on Promo] =[Promo Sales] / ([Regular Sales] + [Promotional Sales])
Results:
Mathematical Operator in
In a
< (Less than)
>(Greater than)
<= (Less than or equal to)
>= (Greater than or equal to)
<>(Not equal to)
Evaluation of Multiple
All of the
In other words, a row must match every
If you need an “OR()” style of operation, you can use the “||” operator. For instance:
CALCULATE([Total Sales], Sales[TransType]=1 || Sales[TransType]=3)
ALL() – The “Remove a Filter” Function
The Basics
The ALL() function is used within a CALCULATE(), as one of the
Let’s jump straight to an example. Consider the following pivot: [Net Sales] displayed by MonthNum, with Year on a slicer:
OK, time for a new measure:
[All Month Net Sales] =CALCULATE([Net Sales], ALL(Sales[MonthNum]))
And the results:
The Practical Basics
Let’s do a simple ratio of the two measures already on the pivot:
[Pct of All Month Net Sales] = [Net Sales] / [Month Net Sales]
Results:
We can remove the original ALL measure from the piv