Excel Magic Trick 1494

DAX & Power Pivot: Allocate Invoice Header Amounts To Invoice Transaction Line Table

DESCRIPTION

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 Topics:

1. Introduction

2. Import Tables and Build Relationships

3. DAX Calculated Column for Invoice Sales in Header Invoice Level Table. SUMX & RELATEDTABLE DAX Functions

4. DAX Calculated Column for Invoice % Discount in Header Invoice Level Table. DIVIDE DAX Function.

5. DAX Calculated Column for Line Level Discount in Line Invoice Level Table. RELATED DAX Function.

6. Look at an Implicit Measure. Not Good.

7. DAX Measure for Total Discount. SUM DAX Function.

8. PivotTable with Total Discount by Product

9. DAX Calculated Column for Invoice Weight in Header Invoice Level Table. SUMX, RELATEDTABLE & RELATED DAX Functions.

10. DAX Calculated Column for Line Level Shipping in Line Invoice Level Table. Three RELATED DAX Functions in one formula.

11. DAX Measure for Total Shipping. SUM DAX Function.

12. PivotTable with Total Shipping by Product.

13. Summary

WORKBOOKS

Download “Excel Magic Trick 1491-1500” Excel-Magic-Trick-1491-1500.zip – Downloaded 127 times – 10 MB







Be the first to comment

Leave a Reply