Category: DAX

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

EMT 1493 Part 2: Excel Array Formulas Allocate Invoice Header Amounts To Transaction Line Table

Posted on by Annie D

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

View on YouTube

EMT 1493: Excel Formulas PivotTable: Allocate Invoice Header Amounts To Transaction Line Table

Posted on by Annie D

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

View on YouTube

DAX Approximate Match Lookup Part 2: Foreign Key & Relationship or LOOKUPVALUE? EMT1492

Posted on by Annie D

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

View on YouTube

DAX Approximate Match Lookup & Exact Match Lookup Together for Discounted Product Price (EMT 1490)

Posted on by Annie D

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

View on YouTube

Mr Excel & excelisfun Trick 190 Inventory w Subtotal, MINIFS, Power Pivot, AGGREGATE, or Power Query

Posted on by Annie D

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

View on YouTube

Excel Magic Trick 1454 DAX Measure Count Customer Totals Between Upper Lower Limits, Each Month

Posted on by Annie D

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

View on YouTube

Excel Magic Trick 1453 Array Formula Count Customer Totals Between Upper Lower Limits, Each Month

Posted on by Annie D

Count Customer Totals Between Upper & Lower Limits For Each Month using an Excel Array Formula and the functions SUMPRODUCT and SUMIFS. Create a Cross Tabulated Frequency Distribution that shows a count of Customer Totals for each month and each Sales Category.
Download File: http://ift.tt/2vP7LBc
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

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

Excel Magic Trick 1449: DAX Measure % Change MOM & YOY Formulas For Incomplete Years

Posted on by Annie D

Excel Magic Trick 1449: Why Standard PivotTable Show Values As % Change Does Not Work for Incomplete Years and How To Fix It with DAX Formulas
Download File:
Start File: http://ift.tt/2uqGsLB
Finished File: http://ift.tt/2vCmsEe
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Video shows how to create DAX Formulas for Change & % Change formulas for both Month Over Month and Year Over Year.
Extensive discussion about the hows and whys of how the formula calculates. In particular we discuss how Filter Context and Row Context Work together in SUMX to get the correct amount for each time period.
See how to
1. (00:10) Introduction
2. (00:40) Look at Change and % Change “Show Values As” Standard PivotTable Calculations and why they are not correct when you have incomplete years, like is often the case with Dashboards & Reports.
3. (01:53) First insights about what the DAX formula will be required to doing order to calculate the correct % Change.
4. (03:07) Look at Excel Tables and Data Model and first Measure that have already been created
5. (03:45) Start Data Model PivotTable
6. (04:28) Theory about calculating Sales Last Year
7. (04:43) Discussion about Filter Context and how DAX Measures evaluate / calculate. Discussion and Visuals about how Criteria in Row Area of PivotTable (also known as Filter Context) flows across relationships to create filter Sales Tables that have fewer rows than the original data set so that DAX Formulas can calculate more quickly on Big Data.
8. (05:23) Introduce CALCULATE DAX Function (functions that can change the Filter Context) and SAMEPERIODLASTYEAR DAX Function (generates a valid list of dates from last year’s period).
9. (05:42) First attempt at DAX Measure to calculate “Sales Last Year” using CALCULATE and SAMEPERIODLASTYEAR.
10. (07:11) Diagram of what the SAMPERIODLASTYEAR Function does to the Filter Context Criteria.
11. (07:43) Problem with first attempt at “Sales Last Year” DAX Measure: It shows values for periods that have no current sales, and Total for the last year in data set adds all 12 months from previous period.
12. (08:04) Introduction to SUMX (iterates a formula over a table and then adds the results).
13. (08:54) Second attempt at “Sales Last Year” using SUMX, VALUES and IF DAX Functions
14. (09:15) Introduction to VALUES DAX Function (yields a unique list of items for a column in the Current Filter Context).
15. (10:51) Error from SAMPERIODLASTYEAR and how to use IF and HASONEVALUE DAX Functions to fix it.
16. (12:01) Look at “Sales Last Year” Measure in PivotTable and see how it is yielding the correct answer
17. (12:35) “Sales Lat Year” Formula: Extended discussion with Visuals and Diagrams to understand how VALUES generates a different size table in each cell in the PivotTable and how SUMX iterates over the VALUES generated table to calculate the correct answer in each cell in the PivotTable.
18. (15:42) Problem with “Total Sales” formula when using it to calculate the change between this year and last year.
19. (16:34) “Comparison Total Sales” DAX Formula to calculate correct Total Sales for the Numerator in the “% Change” formula. See the functions, SUMX, VALUES and IF
20. (18:07) DAX Formula to calculate the amount of change in Sales from Last Year and This Year. “Change” Formula.
21. (19:04) “% Change” DAX Formula to calculate the % change between last year’s period and this year’s period. See DIVIDE DAX Function.
22. (20:14) Add New Data to Sales Table to see that formulas will adjust.
23. (21:41) Summary

View on YouTube

Power BI for Accountants: Complete Introduction to Power BI Desktop to Build Reports & Dashboards

Posted on by Annie D

Complete Introduction to Power BI Desktop. This video will teach you about how to use Power BI Desktop to clean, transform data and make a Dashboard to Monitor Sales. Take multiple Text files and import them into a single table which can be used for Reports and Dashboards and can be updated easily when new data arrives.
Download Files:
Zipped Folder file Text Files: http://ift.tt/2uczRVa
Excel File: http://ift.tt/2vEnQFN
Finished File: http://ift.tt/2uc5gXA
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Excel Accounting Seminar: 2017 WAATP Tacoma Accounting Conferences at La Quinta Inn, July 12
This is the third video in a series of three videos.
Topics in Video:
1. (00:10) Download Files
2. (00:24) Video Introduction and review of Topics covered in video
3. (03:13) Introduction to Power BI Desktop
4. (04:15) Overview of process of using Power BI Desktop to build our specific Reports and Dashboards
5. (05:09) Open and Save Power BI File
6. (06:28) Import Excel dProduct Lookup Tables
7. (08:05) Import and Append Multiple Text Files with Sales Data
8. (15:11) Clean Data
9. (16:42) Discussion of Relationships and why they are important
10. (17:58) Create Calendar Table using DAX CALENDAR Table Function
11. (21:27) Create Calculated Columns with DAX Functions: MONTH, FORMAT and YEAR
12. (21:54) Create Relationships and discuss more about Relationships
13. (26:25) Save As to save Power BI File
14. (26:54) Create Cross Tabulated Power BI Report
15. (28:11) Show Values As % of Column Total
16. (30:35) Create Gross Profit Dash Board
17. (31:29) DAX Measure for Gross Profit %
18. (34:24) Column and Bar Chart as Slicer for Gross Profit Dashboard
19. (36:13) Dashboard Interactivity
20. (38:00) Add New Data And Watch Dashboard Update
21. (39:53) Conclusion & Summary
Event Sponsored by:
Puget Sound/Tacoma Chapter WAATP
Location:
La Quinta Inn
1425 E 27th St,
Tacoma, WA 98421
Event Details:
Wednesday, July 12 at 8 AM

View on YouTube

Excel Magic Trick 1447: DAX DISTINCTCOUNT & CONCATENATEX to Count & List Stores Visited

Posted on by Annie D

Download File:
Start File: http://ift.tt/2tSGcWC
Finished File: http://ift.tt/2tSGcWC
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
See how to use the : DAX functions DISTINCTCOUNT & CONCATENATEX to Count & List Stores that Customers Have Visited.

View on YouTube