Two Fact Tables - DAX, Power Query or Worksheet Formulas to Convert to 1 Fact Table

) Introduction.
) Excel Worksheet Formula Solution.
) How do we allocate Discount from Invoice Grain to Invoice Line Grain?.
) Worksheet Formula for Total Invoice Sales at Invoice Grain using SUMPRODUCT function.
) Worksheet Formula for % Sales Discount at Invoice Grain using division.
) Worksheet Formula for Line Discount at Invoice Line Grain using VLOOKUP and multiplication.
) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?.
) Worksheet Formula for Invoice Line Shipping Weight at Invoice Line Grain using VLOOKUP and multiplication.
) Worksheet Formula for Invoice Weight at Invoice Grain using SUMIFS.
) Worksheet Formula for Line Shipping at Invoice Line Grain using VLOOKUP and multiplication.
) Create Excel Reports at Product Grain..
) Standard PivotTable Report.
) Worksheet Formula Report.
) DAX Formula Solution in Power Pivot.
) Look at Data Model and preview of DAX Formulas and functions SUMX, RELATED and RELATEDTABLE.
) Bring Excel Tables into Data Model.
) Create Relationships between tables.
) How to Allocate Invoice Grain Numbers to Invoice Line Grain Numbers.
) DAX Formula for Total Invoice Sales at Invoice Grain using SUMX and RELATEDTABLE functions .
) DAX Formula for % Sales Discount at Invoice Grain using DIVIDE function.
) DAX Formula for Line Discount at Invoice Line Grain using RELATED function and multiplication.
) DAX Measure for Total Discount.
) Data Model PivotTable Report for Product Discount.
) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?+.
) DAX Formula for Invoice Weight at Invoice Grain using SUMX, RELATEDTABLE and RELATED..
) 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 Calculations..
) DAX Formula for Line Shipping at Invoice Line Grain using RELATED and multiplication and division. Three RELATED function in one formula.
) DAX Measure for Total Shipping.
) Final Data Model PivotTable.
) Power Query Solution in Power BI Desktop.
) Why Two Fact Tables will not work with all Dimension Tables for Reporting..
) Summary and visuals of steps we need to perform.
) Create blank Power BI Desktop file.
) Import Two Fact Table Data Model from Power Pivot.
) Power Query Formula to calculate Sales at Invoice Line Grain using Table.AddColumn function.
) Power Query Merge to lookup Product Weight at Invoice Line Grain.
) Power Query Formula to calculate Product Shipping Weight at Invoice Line Grain using Table.AddColumn function.
) Power Query Group By feature to aggregate Invoice Sales, Invoice Shipping Weight and all rows in Invoice Line Grain Table for each Invoice Number..
) 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 expanding it will be the Invoice Line Grain)..
) Power Query Formula for % Sales Discount at Invoice Grain using Table.AddColumn function.
) Expand to get back to Invoice Line Grain.
) Note about Unit Price and how it is stored as a Fact because it changes so often..
) Power Query Formula for Line Discount at Invoice Line Grain using Table.AddColumn function and Number.Round.
) Power Query Formula for Line Shipping at Invoice Line Grain using Custom Column using Table.AddColumn function and Number.Round.
) Remove all column we do not need in final Fact Table.
) Load Tables to Data Model, except Invoice Level Table..
) Create DAX Measures for Shipping, Discounts and Sales.
) Create % DAX Measures for Shipping and Discount as a percent of sales. Use the DIVIDE DAX Function..
) Hide Columns from Report View.
) Look at Final Data Model.
) Create Visualization in Power BI Desktop.
) Summary.