Excel 2013 PowerPivot Basics #05: Import External Excel Tables, Build Data Model with SUMX function

Download file: http://people.highline.edu/mgirvin/excelisfun.htm Scroll to down to PowerPivot Section. See how to Import 3 Excel Tables from an External Excel Wordbook File, Build Relationships, Build Calculated Column, Build Calculated Field (Measure) using SUMX function, and compared Calculated Columns and calculated Fields 1. (00:21 min mark) See how to Import 3 Excel Tables from an External Excel Wordbook File into Data Model 2. (05:39 min mark) Build Relationships 3. (07:06 min mark) Format Tables in data Model 4. (07:37 min mark) Build Calculated Column for Net Revenue that uses ROUND and RELATED functions. Calculated Columns use “Row Context” 5. (09:27 min mark) Build Calculated Field (Measure) using SUMX function to allow “Row Context” to occur in a Calculated Field. SUMX is an Iterator function that will allow a formula to iterate through a table and simulate “Row Context” in a Calculated Field (Measure). Even though a Calcualted Field normally only sees Filter Context, you can force the Calculated Field (Measure) to perform Row Context with the Iterative Function SUMX. 6. (12:32 min mark) Compare Calculated Columns and Calculated Fields (Measures) 7. (14:22 min mark) Build PivotTable based on data Model.