Download Excel Files: Start: https://ift.tt/2rwVLQ8 Finished: https://ift.tt/2KRKAuz
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video learn how to take Invoice Shipping and Discount Amounts from an Invoice Level (Header) Transaction or Fact Table and allocate those amounts to the Invoice Line Item Level Fact Table so we can use the Line Level Product Criteria (Filter) to create a Shipping and Discount Amount Report by Product. This is a typical business problem where the grain (granularity) of the two Transaction / Fact Tables is different and in order to use a filter or condition / criteria from the Line Level Transaction / Fact Table we must first allocate the Header Level amounts to the Line Level.
In This video we use Excel Array Formulas to solve this problem. We use the Excel Spreadsheet Functions SUMPRODUCT, SUMIFS and VLOOKUP. We see how to simulate relationships with Excel Spreadsheet Formulas using the SUMPRODUCT function and the SUMIFS Function with a Function Argument Array Operation.
Topics:
1. (00:06) Introduction
2. (02:18) Part 1: Allocate Invoice Discount Amounts. SUMPRODUCT function, Array Calculations and VLOOKUP function.
3. (06:24) Part 2: Allocate Shipping Amounts. SUMIFS, Function Argument Array Operation, SUMPRODUCT, Array Operation, 2 VLOOKUP Functions.
4. (13:54) Create Report with SUMIFS Function
5. (14:47) Summary
Related Videos:
EMT 1493: Excel Formulas & PivotTable: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1493 Part 2: Excel Array Formulas Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1494: DAX & Power Pivot: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1495: Power Query: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1496: Power BI Desktop: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1497: Vote For Your Favorite “Allocate Invoice Header Amounts To Transaction Line Item Table”
Search terms in this video: Header Detail Granularity Reporting Problem, Invoice Level, Invoice Detail Level Mismatch, Invoice Granularity Mismatch Reporting Issue, Granularity Invoice Reporting Problem: Invoice Total / Invoice Detail, Reporting Invoice Shipping & Discount at Invoice Detail Level?, Allocating Invoice Totals to Invoice Detail Level (Granularity Reporting Problem), Header Detail Granularity Reporting Invoice Example, Header/Line Item Transactions, Header / Line Item Transactions Reporting Issues, Allocating Invoice Shipping & Discount to Product Report, Allocating Invoice Shipping & Discount to Invoice Line Level, Allocating Invoice Shipping Discount to Invoice Line Level, Two Fact Tables, Different Granularity, How To Allocate Header Amounts to Line Item Fact Table so we can Slicer by Product?, Allocate Invoice Header Amounts, To Transaction Line Item Table, Two Transaction Tables, Different Granularity, Slice Report by Product
Excel Magic Trick 1493