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

preview_player
Показать описание
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. (00:06) Introduction
2. (03:30) Import Tables and Build Relationships
3. (06:43) DAX Calculated Column for Invoice Sales in Header Invoice Level Table. SUMX & RELATEDTABLE DAX Functions
4. (09:28) DAX Calculated Column for Invoice % Discount in Header Invoice Level Table. DIVIDE DAX Function.
5. (10:56) DAX Calculated Column for Line Level Discount in Line Invoice Level Table. RELATED DAX Function.
6. (11:44) Look at an Implicit Measure. Not Good.
7. (13:58) DAX Measure for Total Discount. SUM DAX Function.
8. (11:55) PivotTable with Total Discount by Product
9. (15:21) DAX Calculated Column for Invoice Weight in Header Invoice Level Table. SUMX, RELATEDTABLE & RELATED DAX Functions.
10. (19:25) DAX Calculated Column for Line Level Shipping in Line Invoice Level Table. Three RELATED DAX Functions in one formula.
11. (21:40) DAX Measure for Total Shipping. SUM DAX Function.
12. (22:00) PivotTable with Total Shipping by Product.
13. (22:18) 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 1495 Part 02: Power Query w Group By Rows: Allocate Invoice Amounts To 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 1494
Рекомендации по теме
Комментарии
Автор

Mike, your this video doesn't need thumbs up, but all your 3000+ videos needs double thumbs up. You are a superman, when it comes to EXCEL, DAX, POWER QUERY, POWER BI, and specially ARRAY formulas. In short, all your videos requires big thumbs up. EXCELISFUN is the best source when it comes to serious excel users.

Al-Ahdal
Автор

You are an absolute amazing teacher i just wanted to apologise because these days i didn't watch your videos i was in vacation and no chance to see your videos thanks for the info and u are always the best I'm back again now I can see all ur videos

ismailismaili
Автор

DAX and Power Pivot made this so easy and they way you make us understand made this more easier.

nishantkumar
Автор

WOW! Just: WOW!
Much more efficient than my DAX solution to this problem (it was a direct translation from the first video formulas).
The sheer efficiency is down-right amazing.
It’s fairly simple to perform and super-fast calculating.
This solidifies my vote for the DAX-solution — unless PQ performs a miracle...
Thank you, Mike!

GeertDelmulle
Автор

I learned so much and like the comment from Bart (below) I am so much clearer on RELATED (n:1) and RELATEDTABLE (1:n). Awesomeness made flesh! Thanks for the great education you are providing me!!

petermyran
Автор

Such thorough step by step explanations. I had to rewind a few times to help me understand. Thank you!!

Sal_A
Автор

Thanks for "relating" (pun intended!) the DAX formulas to VLOOKUP's & arrays in the previous video's because it makes the DAX clearer for me. Am looking forward to the rest of the videos and voting!

RobMichaels
Автор

Great trick. watching it over and over again!!

johnborg
Автор

OMG I finally understand why SUMX exists! Thank you, Mike!

Barhomopolis
Автор

I've been studying DAX this year (Power Query Academy). Thanks for the DAX solution Mike!

MySpreadsheetLab
Автор

Wow Mike, this was really a great video on DAX. I finally understand now the difference between RELATED (n:1) and RELATEDTABLE (1:n). Very good choice to use Invoices and Invoice lines together!! One remark: I like all the evaluation methods in Excel (like preview results with F9). DAX does not have that, so I would make separate columns to check the intermediate result. It might be a mater of performance to use as less columns as possible, but the first concern is to get DAX working. So I prefer extra columns in stead of long formula's. Looking forward to the other solutions of this invoice problems. At this moment I go for the DAX solution, I tried PQ but there were many steps....:)

barttitulaerexcelbart
Автор

Great video as always and useful information when to use relatedtable and related. More DAX videos please!

aronmozejko
Автор

Thanks for you videos.. I learnt so many trick and formulas ... Thank you once again... Kindly give some access tutorials for beginner... 😁

sumitrasree
Автор

Wow thanks for sharing and explain related and retaledtable function in PP...
Thanks for PP magic....

entertainmentgalaxy
Автор

Hi Mike! Great video as always...
For your information, Power Pivot is now available for O365 Home Edition if you opted in for the insider program.

Unirotovibe
Автор

Thanks Mike... Mike i wonder if we use related function and get 3 rows . How can i get the third row or the last row value of column total unit

educational
Автор

I just love DAX... Absolutely fun writing dax formulas...any reason to pick RELATEDTABLE over CALCULATETABLE??... I would have choosen the later... As it is more versatile and used in many many other requirements / complex formulas

rrrprogram
Автор

Less complicated than the CrRay(Cr=crazy, Ray=Array) formulas. Pretty cool

thiernoibrahimadiallo
Автор

I'm Creating a Record System in Excel. I want know all bills in different dates about a Customer with the Customer ID. What function I've to Use. Please Help me.

UGComputerVision
Автор

a lot of fun a lot of khnoledge with mike

mohamedchakroun