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

Download Files: https://people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm 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.