Subscribe to the new YouTube channel dedicated for English content

PP01: Introduction to Power Pivot

The tutorial is answering the following questions: What is Power Pivot? In what case you may need Power Pivot? and how to activate it?
Then the session will take you one step deeper to demonstrate the One to Many Relations, how to add a calculated columns in Power Pivot? what is DAX functions? and how to assign number format to a DAX measure
You will learn all the above through a very simple example practicable excel example, you can download the working file and follow along or practice after watching the tutorial

PP02: Data Extraction & Modeling

4 ways to import data to the data model
Work with 10 million rows of data, you won't believe the performance
Star Schema is the best for Excel Data Model
Difference between Implicit measure & Explicit measure
Use 3 DAX functions (Sum – Count – Left) to build a simple report
3 Different places to write DAX measures

PP03: DAX Iterator VS. Calculated COLUMNS

Understand Filter Context
Measures and Calculated Columns
DAX Iterator functions (x-family)
Internal and External filters

PP04: Calendar Tables

Importance of CALENDAR TABLES
Create a Calendar Table using Sheet Functions (Dynamic Array)
Create using a Calendar Table Power Query
Automatically create a Calendar Table in Power Pivot Window
Integrate Calendar Tagle with DATA MODEL

PP05: MODEL Design & 3 Critical Tips

DATA MODEL design
Add MEASURES & build a report
Tip#1: hide columns from CLINET TOOL
Tip#2: SORT BY COLUMN
Tip#3: update CALEDANR table

PP06: Time intelligence Functions “PART I” year on year change

Influence External Filter – CALCULATE function
Year on Year Change – SAMEPERIODLASTYEAR function
Avoid Division by Zero – DIVIDE function
Pivot Tables and Conditional Formatting

PP07: Time intelligence functions “PART II” shift dates - DAX (DATEADD)

Shift dates - DATEADD function
Calculate change% Quarter over Quarter
Calculate change% Month over Month
Calculate change% Week over Week

PP08: Time intelligence Functions “PART III” Running Total

Monthly running total: DATESMTD function
Quarterly running total: DATESQTD function
Yearly running total: DATESYTD function
Yearly running total for last year!
Dynamic sales DASHBOARD

PP09: Actual vs Budget | 2 Fact Tables Inside Same Data Model

Add budget files to the model using Power Query
Handle 2 fact tables inside the same model
Model structure & granularity of the data
Add budget measures
Modify the DASHBOARD and add more budget years

PP10: Convert Pivot Table to Sheet Formulas | CUBEMEMBER & CUBEVALUE

Convert a Pivot Table into Sheet Formulas
Change report layout and format
Update formulas with the new data added!!
Add slicers and charts to the report in absence of a pivot table!!
Retrieve data directly using CUBEVALUE Function

PP11: DAX | All vs ALLSELECTED vs VALUES | % of Total

ALL function to calculate % of grand total
ALLSELECTED function to calculate % of table grand total
VALUES function to calculate % of total rows
VALUES function to calculate % of total column

Home

Page updated

Report abuse