1 ) Introduction 2:18
2 ) Excel Worksheet Formula Solution 2:25
3 ) How do we allocate Discount from Invoice Grain to Invoice Line Grain? 0:43
4 ) Worksheet Formula for Total Invoice Sales at Invoice Grain using SUMPRODUCT function 3:37
5 ) Worksheet Formula for % Sales Discount at Invoice Grain using division 0:36
6 ) Worksheet Formula for Line Discount at Invoice Line Grain using VLOOKUP and multiplication 2:11
7 ) How do we allocate Shipping from Invoice Grain to Invoice Line Grain? 1:14
8 ) Worksheet Formula for Invoice Line Shipping Weight at Invoice Line Grain using VLOOKUP and multiplication 1:02
9 ) Worksheet Formula for Invoice Weight at Invoice Grain using SUMIFS 0:33
10 ) Worksheet Formula for Line Shipping at Invoice Line Grain using VLOOKUP and multiplication 1:45
11 ) Create Excel Reports at Product Grain. 0:12
12 ) Standard PivotTable Report 0:49
13 ) Worksheet Formula Report 3:32
14 ) DAX Formula Solution in Power Pivot 1:03
15 ) Look at Data Model and preview of DAX Formulas and functions SUMX, RELATED and RELATEDTABLE 1:59
16 ) Bring Excel Tables into Data Model 0:44
17 ) Create Relationships between tables 1:07
18 ) How to Allocate Invoice Grain Numbers to Invoice Line Grain Numbers 0:32
19 ) DAX Formula for Total Invoice Sales at Invoice Grain using SUMX and RELATEDTABLE functions 2:43
20 ) DAX Formula for % Sales Discount at Invoice Grain using DIVIDE function 1:09
21 ) DAX Formula for Line Discount at Invoice Line Grain using RELATED function and multiplication 1:06
22 ) DAX Measure for Total Discount 0:41
23 ) Data Model PivotTable Report for Product Discount 0:28
24 ) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?+ 1:13
25 ) DAX Formula for Invoice Weight at Invoice Grain using SUMX, RELATEDTABLE and RELATED. 1:33
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… 0:55
27 ) DAX Formula for Line Shipping at Invoice Line Grain using RELATED and multiplication and division. Three RELATED function in one formula 2:10
28 ) DAX Measure for Total Shipping 0:21
29 ) Final Data Model PivotTable 0:56
30 ) Power Query Solution in Power BI Desktop 0:46
31 ) Why Two Fact Tables will not work with all Dimension Tables for Reporting. 1:55
32 ) Summary and visuals of steps we need to perform 1:06
33 ) Create blank Power BI Desktop file 0:27
34 ) Import Two Fact Table Data Model from Power Pivot 0:59
35 ) Power Query Formula to calculate Sales at Invoice Line Grain using Table.AddColumn function 1:12
36 ) Power Query Merge to lookup Product Weight at Invoice Line Grain 0:32
37 ) Power Query Formula to calculate Product Shipping Weight at Invoice Line Grain using Table.AddColumn function 0:15
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. 2:09
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… 0:59
40 ) Power Query Formula for % Sales Discount at Invoice Grain using Table.AddColumn function 1:18
41 ) Expand to get back to Invoice Line Grain 0:19
42 ) Note about Unit Price and how it is stored as a Fact because it changes so often. 1:44
43 ) Power Query Formula for Line Discount at Invoice Line Grain using Table.AddColumn function and Number.Round 0:32
44 ) Power Query Formula for Line Shipping at Invoice Line Grain using Custom Column using Table.AddColumn function and Number.Round 2:33
45 ) Remove all column we do not need in final Fact Table 0:44
46 ) Load Tables to Data Model, except Invoice Level Table. 1:06
47 ) Create DAX Measures for Shipping, Discounts and Sales 0:54
48 ) Create % DAX Measures for Shipping and Discount as a percent of sales. Use the DIVIDE DAX Function. 1:04
49 ) Hide Columns from Report View 0:17
50 ) Look at Final Data Model 0:13
51 ) Create Visualization in Power BI Desktop 2:52
52 ) Summary