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 805 times – 98 MB







Related Articles

Excel Is Fun

Excel Magic Trick 1220

How To Put Double Quotes In Text Formula: Extra Double Quotes Or VLOOKUP DESCRIPTION Learn how to either use IF function and Extra Double Quotes or VLOOKUP to put the text 5’6” or 6’ into [Read More >>>]

Excel Is Fun

Excel Magic Trick 1128

DESCRIPTION See how to create an automated Accounting Ageing Accounts Receivable Reports On Multiple Sheets With Array Formula, Excel Table Feature and Formula Table Nomenclature (Structured References): (00:16) Overview of end result: Automatic Ageing Reports [Read More >>>]

Dueling Excel

Dueling Excel 136

DESCRIPTION Variable Rent Calculation Based On Days: VBA, IF or VLOOKUP? See Mr Excel and excelisfun use two methods to calculate the total rent for a container based on a sliding scale: 1. VBA with [Read More >>>]

Be the first to comment

Leave a Reply