Loading...

Highline Excel 2016 Class 22: How To Build Data Model & DAX Formulas in Power Pivot

22,572 views

Loading...

Loading...

Transcript

The interactive transcript could not be loaded.

Loading...

Rating is available when the video has been rented.
This feature is not available right now. Please try again later.
Published on Jun 23, 2016

Download Files: https://people.highline.edu/mgirvin/A...
In this video learn about:
1. (00:16) Introduction to Entire Project, including what type of start files we have and what the final report looks like.
2. (03:36) What is Data Modeling? Steps to creating a Data Model.
3. (05:07) Data Modeling Step 1: Power Query to Clean, Transform & Import Fact Tables
4. (11:22) Data Modeling Step 1: Import Dimension Tables from an Excel Sheet
5. (12:23) What is a Calendar Table (Dimension Table)? Why a Calendar Table and not Group by Date?
6. (15:14) Data Modeling Step 1: Create Calendar Table in Excel & Import to Data Model
7. (17:45) Data Modeling Step 2: Create Relationships between Related Tables
8. (19:30) Data Modeling Step 3: Create DAX Calculated Columns in Calendar Table. Dee the DAX functions: MONTH, FORMAT, YEAR, ROUNDUP, and IF. See how to calculate Calculated Columns for Month Number, Month Name, Year, Quarter, Fiscal Quarter, Fiscal Year and Fiscal Period.
9. (21:40) What is Row Context?
10. (30:45) Data Modeling Step 3: Create DAX Calculated Columns in Fact Table for Revenue. See the functions ROUND, RELATED
11. (34:02) Data Modeling Step 3: Create DAX Measures using SUM function n to add values from Calculated Column.
12. (36:33) Data Modeling Step 3: Alternative Total Revenue Calculation: DAX Measure with SUMX. Learn how to perform Row Context in a DAX Measure. This formula calculates total revenue without a Helper Column.
13. (39:18) DAX Calculated Column or DAX Measure to calculate Total Revenue?
14. (40:45) Data Modeling Step 3: More DAX Measures. SUMX to calculate Total COGS. DAX Measure for Gross Profit.
15. (44:25) Data Modeling Step 4: Hide Tables & Fields not used in PivotTables
16. (46:15) Data Modeling Step 5: Create PivotTables and Pivot Charts and Final Dashboard
17. (47:41) What is Filter Context?
18. (47:41) Advantage of Power Pivot Data Model Columnar Database & Relationships & DAX Measures when you have Big Data.
19. (53:55) Data Modeling Step 6: Refresh Data Model when Source Data Changes
20. (54:49) Data Modeling Step 7: Fix Calendar Table
21. (55:50) Data Modeling Step 7: Dashboard After Refreshing
22. (56:11) Data Modeling Step 7: Create new DAX Formulas and create New Report.

Loading...

Advertisement

to add this to Watch Later

Add to

Loading playlists...