In this video we give away a free book after the voting about which method was preferred in Allocating Header Invoice Amounts to the Line Item Transaction Table in these videos:
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”
Tag: DAX
EMT 1497: Vote For Your Favorite “Allocate Invoice Header Amounts To Transaction Line Table”
Vote Here: https://ift.tt/2xfRczM
Download Excel File: https://ift.tt/2KRKAuz
Download Excel File: https://ift.tt/2x73V7S Finished.xlsx
Download Excel File: https://ift.tt/2J2pHi5 Finished.xlsx
Download Excel File: https://ift.tt/2GFTSX2
Download Excel File: https://ift.tt/2J2daeu Finished.xlsx
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.
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”
Excel Magic Trick 1497
EMT 1495 Part 02: Power Query with Group By Rows: Allocate Invoice Amounts To Line Item Table
Download Excel Start File:https://ift.tt/2GFTSX2
Download Excel Finished File: https://ift.tt/2J2pBXM
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 Power Query to take the three tables and convert them using Merges (Joins) and Custom Columns in Power Query into our final report. This video is different than Part 01 because we use Group By Rows to avoid two merges. This trick comes from Bill Szysz.
Topics in this video:
1. (00:06) Introduction
2. (01:47) Compare Steps from first video to the steps we do this this video
3. (03:00) Start Query From Excel
4. (04:09) Duplicate Import Source Data
5. (04:40) Multiply Columns using Multiply feature to calculate “Line Sales”
6. (05:51) Merge to lookup Product Weight
7. (06:57) Multiply Columns using Multiply feature to calculate “Line Weight”
8. (07:14) Group By to aggregate Line Sales and Line Weight in order to get Invoice / Header Level Amounts, but we also Group By Rows to save the Line Item Level data so we can use it later in the query
9. (08:59) Merge to lookup Invoice Level Shipping and Discount Amounts
10. (10:31) Divide Columns using Divide feature to calculate “Invoice % Discount”
11. (11:31) Expand Group By Rows step from earlier in query to get line item detail
12. (12:30) Multiply Columns using Multiply feature to calculate “Line Discount”
13. (12:54) Create Custom Column to calculate “Line Shipping Costs”
14. (13:37) Edit the previous two columns by editing the Table.AddColumn function and add the Power Query Function Number.Round
15. (15:00) Group By to get Final Product Report
16. (16:50) Summary
Other 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 with 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 1495 Part 02
EMT 1496: Power BI Desktop: Allocate Invoice Header Amounts To Transaction Line Item Table
Download Excel Files: Start: https://ift.tt/2k9xwnR Finished: https://ift.tt/2rVV43k
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 Power BI Desktop to allocate Header Invoice Level Amounts down to the Line Item Level Table using both DAX Formulas and Functions and Power Query Functions (M Code), then we create our Interactive Visualizations.
Topics:
1. (00:06) Introduction
2. (01:04) Differences in Power BI Desktop & Power Pivot Excel
3. (01:21) What we need to do to Allocate the amount in Power BI Desktop & DAX
4. (03:23) See how to use Edit Queries to access Power Query (in Power BI Desktop) and Use Add Columns, multiply feature to create a Custom Column to calculate Line Sales in the Line Item Table using the Power Query Function (M Code) Table.AddColumn
5. (06:48) Sort a column in Power Query
6. (07:10) Add Invoice % Discount DAX Calculated Column in the Header Table using DIVIDE, SUMX & RELATEDTABLE DAX Functions
7. (9:20) Add Invoice Weight DAX Calculated Column in the Header Table using SUMX & RELATEDTABLE & RELATED DAX Functions
8. (11:00) DAX Calculated Column for Line Discount using multiplication and DAX RELATED Function
9. (11:30) ) DAX Calculated Column for Line Shipping using multiplication & three DAX RELATED Functions in the same formula!!!
10. (12:40) The different Power BI Icons for Measure & Calculated Columns as compared to Excel Power Pivot.
11. (12:57) Create Measures in Power BI Desktop. Measures for Total Discount & Shipping
12. (14:27) Hide Tables and Columns so they are hidden in Report View
13. (15:33) Create Interactive Visualization for Discount & Shipping by Product.
14. (17:40) 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 1496
EMT 1495: Power Query: Allocate Invoice Header Amounts To Transaction Line Item Table
Download Excel Files: Start: https://ift.tt/2rJIEMK Finished: https://ift.tt/2wL7vEB
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 Power Query to take the three tables and convert them using Merges (Joins) and Custom Columns in Power Query into our final report.
Topics:
1. (00:07) Introduction & Problem Setup
2. (02:08) Power Query Solution including multiple Mergers (Joins) and Custom Columns.
3. (04:33) Duplicate Query
4. (04:51) F2 key to re-name column
5. (05:00) Add Custom Column using Add Column, Multiply to calculate Line Sales which will automatically add Table.AddColumn Power Query Function (M Code Function)
6. (07:45) Use Merge feature (Join feature) to “lookup” Product Weight.
7. (09:49) Add Custom Column using Add Column, Multiply to calculate Line Weight.
8. (10:21) Use Group By feature to add Sales and Weight for each Invoice. This is a Power Query feature to aggregate by a condition that is similar to PivotTables or SUMIFS in Excel.
9. (12:14) Use Merge feature (Join feature) to “lookup” Invoice Discount & Invoice Shipping
10. (13:10) Add Custom Column using Add Column, Divide to calculate Invoice % Discount.
11. (14:18) Merge Header Invoice Level Table with Line Item Table.
12. (15:30) Second Consecutive Merge, now to get Weight from dProduct Table.
13. (16:04) Add Custom Column using Add Column, Multiply to calculate Line Discount
14. (16:29) Add Custom Column by manually typing out formula for Line Shipping.
15. (18:07) Group By to get Final Product Report with correct amounts for Shipping and Discount
16. (19:03) Load Report to Excel Sheet
17. (19:30) 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 1495
EMT 1494: DAX & Power Pivot: Allocate Invoice Header Amounts To Invoice Transaction Line Table
Download Excel Files: Start: https://ift.tt/2KYIJUs Finished: https://ift.tt/2rHGjB6
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
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 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
EMT 1493 Part 2: Excel Array Formulas Allocate Invoice Header Amounts To Transaction Line Table
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
EMT 1493: Excel Formulas PivotTable: Allocate Invoice Header Amounts To Transaction Line Table
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 Formulas and PivotTables to solve this problem. We use the Excel Spreadsheet Functions SUMPRODUCT, SUMIFS and VLOOKUP.
Topics:
1. (00:06) Introduction
2. (03:50) Part 1: Allocate Invoice Discount Amounts
3. (11:01) Part 2: Allocate Shipping Amounts
4. (15:52) Create Report with PivotTable
5. (17:00) Create Report with SUMIFS Function
6. 19:59) 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
DAX Approximate Match Lookup Part 2: Foreign Key & Relationship or LOOKUPVALUE? EMT1492
Download Excel File: https://ift.tt/2rtY5bv
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video learn how to do Approximate Match Lookup in a DAX Calculated Column using two different methods. These tricks come from out YouTube Teammates: Geert Delmulle @ YouTube and RRR program @ YouTube.
Topics:
1. (00:01) Introduction & look back to last video where we used MAX Function on Discount Column
2. (04:50) DAX LOOKUPVALUE Function, DAX Approximate Match Lookup
3. (10:41) Foreign Key, Relationship & RELATED Function, DAX Approximate Match Lookup
4. (12:26) Summary
Other Videos about Approximate Match Lookup in DAX:
Excel Magic Trick 1137: Excel & PowerPivot DAX Formula: Approximate Match Lookup Without VLOOKUP
Excel Magic Trick 1398: DAX Formulas for Running Total and % of Running Total & other DAX Tricks
DAX Approximate Match Lookup & Exact Match Lookup Together for Discounted Product Price (EMT 1490)
https://www.youtube.com/watch?v=JHh7BtRugL8
DAX Approximate Match Lookup & Exact Match Lookup Together for Discounted Product Price (EMT 1490)
Download Files:
Excel Start: https://ift.tt/2HT3MKp
Excel Finished: https://ift.tt/2rnmBuf
Power BI Start: https://ift.tt/2HQRnGK
Power BI Finished: https://ift.tt/2rodppn
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video learn how to create a DAX Calculated Column for Exact Match Lookup and Approximate Match Lookup. See how to combine the two lookups in a formula to lookup the correct Volume Discount Product Price. See how to create these DAX Calculated Columns in both Excel Power Pivot and in Power BI Desktop
Topics in this video:
1. (00:01) Introduction and Setup of Data Model
2. (02:49) Exact Match Lookup in a DAX Calculated Column using Relationships and the RELATED DAX Function, in Excel Power Pivot
3. (06:48) Approximate Match Lookup in a DAX Calculated Column using the DAX Functions CALCULATE, MAX and FILTER, in Power BI Desktop
4. (13:16) DAX Formula in a DAX Calculated Column for correct Volume Discount Product Price, in Excel Power Pivot
5. (15:31) DAX Formula in a DAX Calculated Column for correct Volume Discount Product Price using both Exact Match Lookup and Approximate Match Lookup, in Power BI Desktop
6. Summary
Excel Magic Trick 1490
Also see: Excel Magic Trick 1398: DAX Formulas for Running Total and % of Running Total & other DAX Tricks, https://www.youtube.com/watch?v=Ex4MGw-VMWc&t=281s @ 4:19 minute mark
Mr Excel & excelisfun Trick 190 Inventory w Subtotal, MINIFS, Power Pivot, AGGREGATE, or Power Query
Mr Excel & excelisfun Trick 190 Inventory with Subtotal, MINIFS, Power Pivot, AGGREGATE, or Power Query
Download workbook: : http://ift.tt/2jDcKQJ
Question sent in:
I work for a retailer & was asked to create a spreadsheet to show our sales teams what we own & what we can sell. Simple enough right? Well here is the catch. The items we sell contains multiple cartons & are inventoried on a per carton basis.
Bill Mr Excel Jelen and Mike excelisfun Girvin show how to use
Topics:
1. (00:01) Dueling Song
2. (00:40) Explanation of problem
3. (01:50) Subtotals Solution (Mr Excel)
4. (04:00) MINIFS and Helper Column Solution (excelisfun)
5. (05:45) Power Pivot and MINX DAX Formula Solution (Mr Excel)
6. (09:18) AGGREGATE Function with no Helper Column & Array Formula (excelisfun)
7. (12:19) Power Query (Mr Excel)
8. (16:15) Wrap up and conclusion
Excel Duel, Dueling Excel, Excel Duel 190
Excel Magic Trick 1454 DAX Measure Count Customer Totals Between Upper Lower Limits, Each Month
Count Customer Totals Between Upper & Lower Limits For Each Month using a DAX Measure in either Power Pivot or Power BI Desktop. See the DAX Functions: COUNTROWS, FILTER, MAX and MIN and an AND Logical Test. Create a Cross Tabulated Frequency Distribution that shows a count of Customer Totals for each month and each Sales Category.
Download File:
Start File: http://ift.tt/2vu0rIq
Finished File: http://ift.tt/2vLpetY
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Related Videos:
Excel Magic Trick 1453 Array Formula Count Customer Totals Between Upper Lower Limits, Each Month
Excel Magic Trick 1454 DAX Measure Count Customer Totals Between Upper Lower Limits, Each Month