Category: Power Pivot

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

Posted on by Annie D

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

View on YouTube

Introduction to Power Query & Power Pivot Data Model in Excel 2016 (Excel Magic Trick 1468)

Posted on by Annie D

Download Excel File #1: http://ift.tt/2fRzTtj Download Excel File #2: http://ift.tt/2y6qjgT
Download Zipped Folder: http://ift.tt/2fQnypv
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Learn about how
Topics:
1. (00:16) Introduction
2. (00:51) Discussion about the two different Data Ribbon Tabs and Get & Transform groups that you may see in Excel 2016
3. (03:51) Example #1: Clean Data and create Excel PivotTable Report, then add new data and refresh report!
4. (17:06) Example #2: Transform Cross Tabulated Table into Proper Data Set
5. (21:50) Example #3: Import 1.2 Million Rows of Data from Text Files into Power Pivot and create a Data Model PivotTable, then add new data and refresh our report.
6. (22:34) Diagram to understand Data Model and Power Pivot
7. (46:46) Unhanded Exception Error.
8. (48:02) Summary

Reference Video:
Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model
https://www.youtube.com/watch?v=Jzt-I4mt8sQ

View on YouTube

Excel Magic Trick 1451: Hack Data Model, Date Table & DAX Measures WITHOUT buying Power Pivot

Posted on by Annie D

Learn about DAX Functions: CALCULATE, SAMPERIODLASTYEAR, TOTALYTD, DATEADD, IF, AND, HASONEVALUE and More!
Learn How to Define a Variable in a DAX Measure using VAR and RETURN Keywords
Learn how to build a complete Data Model and DAX Formulas without Power Pivot in Excel 2016
Download File:
Start File: http://ift.tt/2unJw83
Finished File: http://ift.tt/2u7PyOO
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs

Topics in This Video:
1. (00:04) How to Download Files.
2. (00:14) Introduction to building Data Model with Relationships and DAX Formulas without buying Power Pivot in Excel 2016.
3. (02:47) Import Tables and Build Relationships using the Relationship button in the Data Ribbon Tab.
4. (04:45) Build Data Table in Excel, including TEXT Function formula to get Months to Sort Correctly in a Data Model PivotTable, without using the Sort By Column feature. See the Fill Series Trick to get a complete list of dates, see the functions YEAR, MONTH and TEXT.
5. (09:23) Add Relationship to Date Table.
6. (09:43) Start building Data Model PivotTable and notice a problem with month names not sorting correctly. Then fix it with the TEXT Function using custom Number Formatting “mm – mmm”.
7. (12:04) Create first DAX Measure (Formula) to add Total Sales. See SUM Function and how to add Number Formatting to Measure.
8. (13:42) What NOT to do when you are hacking the Data Model. Do not drag Sales or Date Fields to Values Area or Row Area of Pivot Table. These are called Implicit Measures and they are not efficient.
9. (14:47) Discuss Formula to get Last Year’s Sales.
10. (15:04) Discussion about Filter Context and how DAX Measures calculate or evaluate to get the correct answer.
11. (16:28) Create Second Measure (Formula) to get Last Year’s Sales using the CALCULATE and SAMEPERIODLASTYEAR DAX Functions.
12. (19:22) How to hide Grand Total Formula with IF and HASONEVALUE DAX Functions. We use HASONEVALUE Function on the Year Column.
13. (22:27) Formula to calculate Difference From Last Year using Two Previously Defined Measures and the IF Function.
14. (24:00) Third DAX Measure (Formula) for Running Total using TOTALYTD, IF and HASONEVALUE function on the Month Column.
15. (27:28) Fourth DAX Measure (Formula) for Difference from Last Month using CALCULATE, DATEADD, IF, HASONEVALUE, and AND DAX Functions. Also see Variable in DAX.
16. (29:50) Create Variable in Excel 2016 DAX Function language using the VAR and RETURN Keywords.
17. (34:26) Summary

Excel Power Pivot, How to Get Power Pivot For Free, How Do I Get Power Pivot?, Build Complete Data Model without Power Pivot, DAX Formulas in Excel, Excel 2016, Highline College, Mike Girvin, excelisfun, Excel Magic Trick, Data Model PivotTables, Excel 2016 Relationships, Relationships in Excel, Multiple Tables in PivotTable Field List, CALCULATE, SAMPERIODLASTYEAR, TOTALYTD, DATEADD, IF, AND, HASONEVALUE, DAX Measures, DAX Formulas, How to Create Variable in DAX in Excel 2016, VAR in Excel, DAX Variable in Excel

View on YouTube