Completed
) Introduction
Class Central Classrooms beta
YouTube playlists curated by Class Central.
Classroom Contents
Two Fact Tables - DAX, Power Query or Worksheet Formulas to Convert to 1 Fact Table
Automatically move to the next video in the Classroom when playback concludes
- 1 ) Introduction
- 2 ) Excel Worksheet Formula Solution
- 3 ) How do we allocate Discount from Invoice Grain to Invoice Line Grain?
- 4 ) Worksheet Formula for Total Invoice Sales at Invoice Grain using SUMPRODUCT function
- 5 ) Worksheet Formula for % Sales Discount at Invoice Grain using division
- 6 ) Worksheet Formula for Line Discount at Invoice Line Grain using VLOOKUP and multiplication
- 7 ) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?
- 8 ) Worksheet Formula for Invoice Line Shipping Weight at Invoice Line Grain using VLOOKUP and multiplication
- 9 ) Worksheet Formula for Invoice Weight at Invoice Grain using SUMIFS
- 10 ) Worksheet Formula for Line Shipping at Invoice Line Grain using VLOOKUP and multiplication
- 11 ) Create Excel Reports at Product Grain.
- 12 ) Standard PivotTable Report
- 13 ) Worksheet Formula Report
- 14 ) DAX Formula Solution in Power Pivot
- 15 ) Look at Data Model and preview of DAX Formulas and functions SUMX, RELATED and RELATEDTABLE
- 16 ) Bring Excel Tables into Data Model
- 17 ) Create Relationships between tables
- 18 ) How to Allocate Invoice Grain Numbers to Invoice Line Grain Numbers
- 19 ) DAX Formula for Total Invoice Sales at Invoice Grain using SUMX and RELATEDTABLE functions
- 20 ) DAX Formula for % Sales Discount at Invoice Grain using DIVIDE function
- 21 ) DAX Formula for Line Discount at Invoice Line Grain using RELATED function and multiplication
- 22 ) DAX Measure for Total Discount
- 23 ) Data Model PivotTable Report for Product Discount
- 24 ) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?+
- 25 ) DAX Formula for Invoice Weight at Invoice Grain using SUMX, RELATEDTABLE and RELATED.
- 26 ) Visuals to understand how DAX Formula with SUMX, REALTEDTABLE and RELATED are working to traverse multiple relationships in one formula. This helps illustrates the Power of DAX for Business Calcula…
- 27 ) DAX Formula for Line Shipping at Invoice Line Grain using RELATED and multiplication and division. Three RELATED function in one formula
- 28 ) DAX Measure for Total Shipping
- 29 ) Final Data Model PivotTable
- 30 ) Power Query Solution in Power BI Desktop
- 31 ) Why Two Fact Tables will not work with all Dimension Tables for Reporting.
- 32 ) Summary and visuals of steps we need to perform
- 33 ) Create blank Power BI Desktop file
- 34 ) Import Two Fact Table Data Model from Power Pivot
- 35 ) Power Query Formula to calculate Sales at Invoice Line Grain using Table.AddColumn function
- 36 ) Power Query Merge to lookup Product Weight at Invoice Line Grain
- 37 ) Power Query Formula to calculate Product Shipping Weight at Invoice Line Grain using Table.AddColumn function
- 38 ) Power Query Group By feature to aggregate Invoice Sales, Invoice Shipping Weight and all rows in Invoice Line Grain Table for each Invoice Number.
- 39 ) Power Query Merge to pull Invoice Grain Shipping & Discount numbers, as well as to pull the Invoice Level Dimensions of Date and Sales Rep ID into the current step in the query (later after expandi…
- 40 ) Power Query Formula for % Sales Discount at Invoice Grain using Table.AddColumn function
- 41 ) Expand to get back to Invoice Line Grain
- 42 ) Note about Unit Price and how it is stored as a Fact because it changes so often.
- 43 ) Power Query Formula for Line Discount at Invoice Line Grain using Table.AddColumn function and Number.Round
- 44 ) Power Query Formula for Line Shipping at Invoice Line Grain using Custom Column using Table.AddColumn function and Number.Round
- 45 ) Remove all column we do not need in final Fact Table
- 46 ) Load Tables to Data Model, except Invoice Level Table.
- 47 ) Create DAX Measures for Shipping, Discounts and Sales
- 48 ) Create % DAX Measures for Shipping and Discount as a percent of sales. Use the DIVIDE DAX Function.
- 49 ) Hide Columns from Report View
- 50 ) Look at Final Data Model
- 51 ) Create Visualization in Power BI Desktop
- 52 ) Summary
