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

Learn how to convert two fact tables into one using DAX, worksheet formulas, and Power Query in this comprehensive tutorial. Explore techniques for allocating discounts and shipping from invoice grain to invoice line grain using Excel functions like SUMPRODUCT, VLOOKUP, and SUMIFS. Dive into DAX formulas in Power Pivot, utilizing functions such as SUMX, RELATED, and RELATEDTABLE to create measures and navigate relationships in the data model. Discover Power Query solutions in Power BI Desktop, including merging tables, creating custom columns, and using the Group By feature to aggregate data. Create visualizations and reports at various levels of granularity, and understand why working with two fact tables can be challenging for reporting. By the end of this tutorial, gain the skills to efficiently combine and analyze complex data structures in Excel and Power BI.

) 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.