Import External Excel Tables, Build Data Model with SUMX function
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.
(00:21) See how to Import 3 Excel Tables from an External Excel Wordbook File into Data Model
(05:39) Build Relationships
(07:06) Format Tables in data Model
(07:37) Build Calculated Column for Net Revenue that uses ROUND and RELATED functions. Calculated Columns use “Row Context”
(09:27) 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.
(12:32) Compare Calculated Columns and Calculated Fields (Measures)
(14:22) Build PivotTable based on data Model.