Overview
Power Pivot Fundamentals Course Overview
This course provides step-by-step instructions for how to set up a business intelligence dashboard in Excel using the Power Pivot add-on. You’ll learn the key functions and best practices for setting up a dashboard. As you progress through the course, you’ll apply everything you’ve learned to a hands-on example using data we provide. By the end, you’ll walk away with everything you need to create your own dashboards in Excel—so you can analyze and present data, track KPIs, and create powerful time-period comparisons.
Power Pivot Fundamentals Learning Objectives
Upon completing this course, you will be able to:- Create a Power Pivot table by drawing data from several different tables
- Develop simple calculated metrics with consistent formatting
- Assemble multiple pivot tables, charts, and dimensions into a dashboard that provide us with different angles of analysis
- Apply DAX formula language to create measures and columns
- Manipulate context to calculate more advanced metrics
- Utilize DAX Advanced Time Intelligence to create period comparisons and KPIs
Who should take this course?
This Power Pivot course is perfect for professionals who have a solid understanding of excel and want to expand Excel’s business intelligence capabilities. This course is designed to equip anyone who desires to begin a career in business analysis—or other roles that require displaying and analyzing data sets—with a fundamental knowledge of this core tool. As of 2020, Power Pivot functionality is not available for the Mac version of Excel. Because of this students must use Windows to complete this training.
Get Recognized With CFI
See All Certification ProgramsWhat you'll learn
Lesson
Multimedia
Exams
Files
A Basic Data Model
Chapter Introduction - A Basic Data Model
Free Preview
Tables in Our Data Model
Free Preview
Database Normalization
Free Preview
Fact & Dimension Recap
Opening the Starter File
Importing Data into Power Pivot
Power Pivot Overview
Create a Basic Pivot Table
Create a Relationship in Power Pivot
Types of Relationships
Power Pivot Table Review
Insert a Slicer
Basic Measures
Chapter Introduction - Basic DAX Measures
Opening and Refreshing the Partially Completed Course File
Two Common Problems When Refreshing Data
Creating an Explicit Measure
Hiding Columns from the Excel View
Creating a Calculated Column for Store Size
Updating the Dashboard and Slicer
Calculating Margin Dollars
Calculating Sales with SUMX
Comparing Methods of Creating Measures
Calculating Margin %
A Closer Look at DAX
More Examples of Context
Working with a Star Schema
Chapter Intro - Working with a Star Schema
The Star Schema
Copy a Pivot Table
Adding the Product Dimension
Add a Pivot Table for Brands
Format Measures
Managing Multiple Slicer Connections
The Datetime Dimension
Tidying the Diagram View
Creating a Pivot Chart
Creating a Slicer to Chart Connections
Removing Autosizing Based on Slicer Selections
Chart Formatting Overview
Changing the Sort Order of Months
Adding a Timeline
Setting up the Basics of the Store Performance Dashboard
Linking Slicers Between Report Pages
The Manager Dimension - Dealing with Duplicates
Load and Transform Data in Power Query
Load data from Power Query to Data Model
Setting up the Manager Pivot Table
From Numbers to Narratives
From Numbers to Narratives
DAX % of Total
Best Practice for Naming Metrics
DAX Time Intelligence - SalesPY
DAX Time Intelligence - Sales Vs PY
Dashboard Consistency
Conditional Formatting in Pivot Tables
Create a KPI in Power Pivot
Modify KPI Icons in Power Pivot
Finishing Touches
Task - Finalize the Store Dashboard
Review - Pivot Table Layout
Review - Advanced Table Sort
Extended Practice
Course Summary
Qualified Assessment
This Course is Part of the Following Programs
Why stop here? Expand your skills and show your expertise with the professional certifications, specializations, and CPE credits you’re already on your way to earning.
Financial Modeling & Valuation Analyst (FMVA)® Certification
Power Pivot Fundamentals is part of the Financial Modeling & Valuation Analyst (FMVA)® certification, which includes 41 courses.
- Skills Learned Financial modeling and valuation, sensitivity analysis, strategy
- Career Prep Investment banking and equity research, FP&A, corporate development
Business Intelligence & Data Analyst (BIDA)® Certification
Power Pivot Fundamentals is part of the Business Intelligence & Data Analyst (BIDA)® certification, which includes 24 courses.
- Skills Learned Data visualization, data warehousing and transformation, data modeling and analysis
- Career Prep Business intelligence analyst, data scientist, data visualization specialist
Data Analysis in Excel Specialization
- Skills Learned Data Modelling & Analysis, Data Transformation, Data Visualization
- Career Prep Data Analyst, Business Intelligence Specialist, Data Scientist, Finance Analyst
Business Intelligence Analyst Specialization
- Skills learned Data Transformation & Automation, Data Visualization, Coding, Data Modeling
- Career prep Data Analyst, Business Intelligence Specialist, Finance Analyst, Data Scientist
What our students say
Very beautifully designed course
I wish i should have done it before moving toward Power BI
I wish i should have done it before moving toward Power BI
Talal Pasha
enjoying the knowledge gain
this has vastly improved my skills and understanding which I hope to apply in my work
this has vastly improved my skills and understanding which I hope to apply in my work
Chiinga Geoffrey Chuba
Great knowledge
With CFI courses, your knowledge grows steadily.
With CFI courses, your knowledge grows steadily.
Taleb Aldris
Practical application of Power Pivot
All techniques taught are applicable in day to day working of any MS Excel user.
All techniques taught are applicable in day to day working of any MS Excel user.
