At the heart of Microsoft BI is Power Pivot, although around since 2007, only now is it becoming a forerunner in data analyses, and there's good reason for it! Previously any serious data analyses was carried out by the office IT administrator, if you are lucky enough to have one, however with Microsoft Power BI, including Power Pivot and Power Query in its armoury, you are now empowered to perform that same analyses yourself.

There are two Power Pivot courses available, introduction and advanced, the intro allows you to comprehend the ability within Power Pivot and confidently manage your reports. The advanced course takes you beyond the basics, looking at multiple table relationships, working with advanced DAX (Data Analyses Expressions). We recommend two days to be truly empowered! You get to grips with Power Pivot, then create reports with pivot tables, pivot charts and dashboards.

We look at loading data into Power Pivot, getting results through Calculated Columns then viewing them in an Excel PivotTable. We will work with the DAX engine to create calculations called measures, this might sound a bit complicated but if you are already familiar with some of Excel's formulas, it is a seamless progression to DAX. DAX is the core capability of Power Pivot, from the perspective of the Excel audience.

The course’s concepts and approach are introduced in a simple, step-by-step manner tailored to the learning style of Excel users everywhere. The techniques presented allow users to produce, in hours or even minutes, results that formerly would have taken entire teams weeks or months to produce.

The lessons look at the difference between calculated columns and measures (DAX); how formulas can be reused across reports of completely different shapes; how to merge disjointed sets of data into unified reports; how to make certain columns in a pivot behave as if the pivot were filtered while other columns do not; and how to create time-intelligent calculations in pivot tables such as “Year over Year”.

Introduction to Power Pivot

This introduction course is designed to give users a solid understanding of the basics of Power Pivot, including multiple table relationships; handle calculated columns; write DAX measures and run reports in pivot table.

  • A Revolution Built On You
  • Power Pivot and the Power BI Family: Making Sense of the Various Versions
  • Learning Power Pivot the Excel Way
  • Loading Data Into Power Pivot
  • Introduction to Calculated Columns
  • Introduction to DAX Measures - Data Analysis Expressions
  • The Golden Rules of DAX Measures
  • CALCULATE() – Your New Favourite Function
  • ALL() – The Remove a Filter Function
  • Thinking in Multiple Tables

Course attendees are required to be regular users of Excel before attempting the introduction Power Pivot courses.

Advanced Power Pivot

This advanced course is designed to give users an understanding of some of the more advanced features in Power Pivot as well as the Microsoft BI system including Power Query and Power View.

  • Disconnected tables
  • Introducing the FILTER() Function, and Disconnected Tables Continued
  • Introduction to Time Intelligence
  • IF(), SWITCH(), BLANK() and Other Conditional Fun
  • SUMX() and Other X “Iterator” Functions
  • Multiple Data Tables
  • Multiple Data Tables – Differing Granularity
  • Performance: Keep Things Running Fast
  • Power Query to the Rescue
  • Power BI Desktop - Microsoft Business Intelligence
  • “Complicated” Relationships
  • Row and Filter Context Demystified
  • CALCULATE and FILTER More Nuances
  • Time Intelligence with Custom Calendars: Greatest Formula in the World

Course attendees are required to be regular users of Excel and confident with the basics of Power Pivot before attempting the advanced Power Pivot courses.