Excel Magic Trick 1322

Backwards One To Many Relationship Report: Excel, Power Query or SQL?

DESCRIPTION

See how to use Excel, DAX, Power Query or SQL to Calculate Average GPA with one condition when the formulas need to ework backwards across a One To Many Relationship between Two Tables:
(00:16) Problem Introduction
(03:17) Excel Array Formula Solution using AVERAGE, IF and COUNTIFS function with a Function Argument Array Operation
(08:29) Excel 2016 Data Model DAX formula (DAX Measure) solution using AVERAGE, AVERAGEX, CALCULATE and FILTER functions, without Power Pivot
(18:37) Power Query (Get & Transform)
(24:11) SQL coding in Access
(30:04) Summary

WORKBOOKS

Download “Excel Magic Trick 1321-1340” Excel-Magic-Trick-1321-1340.zip – Downloaded 824 times – 98 MB







Related Articles

Excel Is Fun

Excel Magic Trick 1090

DESCRIPTION See how to use the MEDIAN function is unusual ways that allow you to shorten the formula (for last two examples): (00:09) Deliver an OR Criteria Logical Value. (02:15) Payroll Tax calculation where there [Read More >>>]

Dueling Excel

Dueling Excel 005

DESCRIPTION See Mr Excel and excelisfun try and create Horizontal Subtotals for a data set. See the IF, SUM and SUMIF functions as they try to solve this very difficult problem. Also see Conditional Formatting [Read More >>>]

Excel Is Fun

Excel Magic Trick 1316

DESCRIPTION See how to use VLOOKUP when there are multiple Lookup Tables with these methods: (00:15) Problem Set up (01:48) IF Function (05:57) IFS Function (Excel 2016 Insider Program) (Formula from XLarium at YouTube) (08:47) [Read More >>>]

Be the first to comment

Leave a Reply