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







Related Articles

Excel Is Fun

Excel Magic Trick 1305

DESCRIPTION Learn how to perform multiple two-way lookups for a cross tabulated table to get multiple sales numbers and then add them using an array formula. Learn how to get INDEX function to deliver an [Read More >>>]

Excel Is Fun

Excel Magic Trick 1451

DESCRIPTION Topics in This Video: (00:04) How to Download Files. (00:14) Introduction to building Data Model with Relationships and DAX Formulas without buying Power Pivot in Excel 2016. (02:47) Import Tables and Build Relationships using [Read More >>>]

Excel Is Fun

Office 2016 13

DESCRIPTION Topics in this video: (00:01) Introduction (01:30) Excel file = “Workbook”, not “Document” (02:22) Columns (Represented by Letters) and Rows (Represented by Numbers) (02:29) Cells = Intersection of Column and Row = Name or [Read More >>>]

Be the first to comment

Leave a Reply