Excel Data Analysis 02 files:
https://excelisfun.net/files/EDA02-St...
https://excelisfun.net/files/EDA02-Fi...
https://excelisfun.net/files/EDA-02-C...
https://excelisfun.net/files/EDA02-Po...
This is a comprehensive and complete lesson in how to use Power Pivot & DAX Formulas to create reports and visualizations in Excel. It teaches the full process of building a Data Model, Building DAX formulas that you can use for dashboard reporting. This video teaches about the concepts that make Power Pivot work like star schema data modeling, filter context and context transition. If you want the how and whys of Power Pivot & DAX formulas, this is the right video for you.
Topics:
1. (00:00) Intro and overview of Standard PivotTable and Data Model Pivot Table
2. (03:06) 5 Steps in the Data Analysis Process
3. (03:38) Step #1: Get Data using Power Query.
4. (06:12) Show Power Pivot Ribbon Tab.
5. (06:56) Power Query to import text “.CSV” files from a folder, including explanation of Applied Steps and automatic queries created.
6. (11:34) Loading data to the Data Model with Import Data dialog box and using the “Only Create Connection” and the “Add this data to the Data Model”.
7. (12:56) Look at data in Data Model. Preview of Power Pivot for Excel window
8. (13:20) What is a Columnar Data? See how Data is compressed to a small size so that the Data Model can handle Big Data.
9. (15:16) Import Excel Tables to the Data Model using Power Query.
10. (15:29) Decimal Power Query Type is the most consistent Power Query Data Type for numbers with decimals, if you want accurate calculations. If the numbers are whole numbers, use Whole Number Power Query Data Type.
11. (17:01) Step #2: Build Star Schema Data Model.
12. (17:17) Create a Power Pivot Date Table.
13. (19:29) Create One-To-Many Relationships between Fact Table and Dimension Tables.
14. (21:48) Create DAX Calculated Column in Date Table for EOMonth.
15. (24:17) First look at ROW Context to make a calculation in each row in a DAX Calculated Column (or DAX Iterator).
16. (26:05) Create Total Sales Measure with 2 Step Process: 1) DAX Calculated Column for Line Sales, then 2) Measure for Total Sales.
17. (28:56) Second look at ROW Context to calculate line sales in each row of the table.
18. (30:25) SUMX Function, Iterator Function, to create Total Sales Measure. This is 1 Step Method.
19. (32:43) Should you use DAX Calculated Column? Or SUMX Iterator function Measure? What are trade offs?
20. (33:37) Step # 3: Build Reports. Build First Data Model PivotTable to show Year Month Sales Report. Start PivotTable from within Power Pivot for Excel window.
21. (34:48) PivotTable Fields task pane for a Data Model PivotTable. Which table is from Data Model?
22. (36:37) What is Filter Context? Visuals and explanations.
23. (38:44) How Filter Context helps with Big Data.
24. (39:55) Why some columns are not needed in PivotTable list.
25. (39:55) Why Implicit Measures are inefficient and cause problems.
26. (41:50) Hide Fields. Hide From Client Tool. Hide in Report View.
27. (42:57) Sort Month Names in Data Model.
28. (43:41) Step # 4: Visualize. Create Line Chart for Year / Month Sales to see sales trends over time.
29. (45:27) Create Power Pivot Data Model PivotTable from Excel Data tab.
30. (46:39) Create Regions / Year Sales Report.
31. (47:02) Create DAX Formula for YOY % Change. Learn about the DAX functions: CALCULATE, SAMEPERIODLASTYEAR, DIVIDE, HASONEVALUE and IF.
32. (47:54) CALCULATE function. Calculate Total Sales for last year.
33. (53:41) Create DAX Formula for Average Monthly Sales by Product and Year.
34. (54:54) Learn about VALUES function to get a unique list and deliver a table.
35. (55:25) Create AVERAGEX formula.
36. (56:33) Context Transition explanation and diagrams to learn how AVERAGEX and VALUES and the Total Sales Measure calculates average monthly sales with a much simpler formula than in the Excel worksheet.
37. (59:07) Warning about Context Transition. Why aggregate formula yields wrong answer in Iterator function, but Measure yields correct answer.
38. (01:00:15) Hidden CALCULATE function in each Measure. CALCULATE performs Context Transition.
39. (01:01:00) Include Zero Values in average using the IF Function in the second argument of AVERAGEX.
40. (01:02:52) Create Cross Tab Report with Data Model.
41. (01:03:13) Create Frequency Distribution with YOY % Change.
42. (01:04:00) COUNTROWS DAX function. Super Charged COUNTIFS and FREQUENCY.
43. (01:04:50) Using Variables to DAX Formulas. YOY % Change for Number Transactions.
44. (01:07:45) DAX Formatter web site.
45. (01:08:08) Frequency Data Model PivotTable.
46. (01:08:39) Step # 5: Get New Data and Refresh.
47. (01:11:33) Publishing to Power BI Online and making Report from a blank Excel Workbook.
48. (01:13:46) Comparing a Power Pivot Data Model PivotTable to a Standard PivotTable.
49. (01:15:28) Summary.
50. (01:16:04) Next Video