Course Overview

Pivot tables have always been the key reason why data analysts choose Excel. So in all their awesomeness two key shortages have lingered in the background.

1. Analyzing data from multiple tables in one Pivot Table (could be done but only if certain conditions were met);
2. Analyzing datasets with more than 1.000.000 rows (there was OLAP, but that was a sort of “what you see is what you get” deal);

Then in 2009 Power Pivot came around and it was a game changer. All of a sudden the user could create data models with multiple data sources and calculate “time intelligent” calculations and running totals with ease and then include all these calculations in a single(!) pivot table.

So this course is all about Power Pivot and empowering attendees with a workshop style learning process by building a Sales Data Model getting data from multiple sources.

Software Requirements

Attendees should have Excel Professional Plus (Pro Plus) 2010 or higher. If they use Excel 2016 Pro Plus or 2013 Pro Plus they need no extra installations, for version 2010 Pro Plus Power Pivot Add-Inn for Excel 2010 will need to be installed.

Course at а Glance

Data Model

• Importing Data From Multiple Sources (Excel, Access, Web, Txt or CSV)
• Fact Tables and Dimensions
• Creating Connections in our Data Model
• Calendar Table
• Sort By Column
• Data Types
• Hide what you don’t need

DAX language and creation of calculations (Measures)

• Basic Calculated Columns
• Basic Calculated Fields (Measures)
• CALCULATE
• The X functions
• time intelligence
  • YTD (Year to Date)
  • PY (Previous Year)
  • PM (Previous Month)
  • Functions for filtering data

Advanced Data Modelling Techniques

• Disconnected Slicers
  • Managing  Calculations
  • New Calculations connected to Disconnected Slicers
• Change Pivot Tables to Functions
  • CUBE functions
  • OLAP Tools in Excel

Sales Data Model

• Comparing Actuals to Plan (Forecast)
• Actual vs. Budget
• Comparing Actuals to previous periods