Excel 2013 PowerPivot Basics #05: 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
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.

[SaniGarba]

The Most EXCEL-lent MVP, is the only way to describe you, Marvin.

[excelisfun]

Excel 2013 PowerPivot Basics #05: 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
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.

[sideshowbobby]

I've been watching your videos for the past two years. Thank you so much.

[sasavienne]

Hi Mike, I am working on improving my skills in Power Pivot and I decided to watch all your Power Pivot Videos. They are amazing. 🌟 🌟 🌟 🌟
I am grateful to you for every piece of information. You are excellent.

[Zwei]

You are one of the great Excel teachers ever! Thank you for the videos.

[excelisfun]

, you Google setting do not allow me to directly reply to your comments.
Glad you like the video!

[pmsocho]

Awesome stuff .Thanks to You and Marco.

[MarkCBB]

Epic Video!!! The formula that does the Unique Count using Frequency is great (So fast). Thank you!!!

[clap]

Hi Mike, good to see you back up and running. Great series so far. Just a tiny errata I noticed - 15:05 you describe Total Net Revenue as the calculated column rather than the calculated field. Just a tiny thing but best to be exact in this particular minefield!  Anyhow, more power to you - I learn more from you than anywhere else. 

[zardhsc]

As always, thank you for this great Excel video.

[waltermiguelfernandez]

Excelente video! gracias por compartir tus conocimientos!

[himanshudalai]

Thank you Mike for this great learning video.

[arnoldiusss]

Great video! (1:40 ctrl-a for shortcut consistency ;-)   )

[pauledkins]

Hi Mike - why do you need to use SUMX?  I used SUM and it worked fine.  My measure definition was:

Sum of Net Revenue:=SUM([Net Revenue])

Thanks for all your videos, I'm learning PowerPivot through them.

[nikhilgupta]

Instead of dragging you can double click to make the column readable without dragging it all the way... Boom!!! :D

[tartarus]

Shift control left arrow should highlight your text for you when naming the tables

[davida.taylor]

I've been using this stuff at work like crazy recently and having a blast learning it since my agency recently upgraded from Office 2007 to Office 2013 - so, many, many thanks for these tutorials. I've read some books and blogs, but nothing is as effective as seeing it in action. Regarding Calculated Columns vs Calculated Fields: Can i create a CF without needing to create a CC? In your example here, could you have created the CF without creating the CC? And where would you put it?

Thanks!

[VanessaKlinger]

Isn't there another option for you to insert an original pivot then select the relationship tab then perform the same function?

[marcuschick]

I've watched all your videos so far and they're great as always.  When you create the Data Model, you always create it in a new workbook, even if the data is stored in Excel.  Is that good practice to ensure smaller file sizes?  Thanks Marcus

[MohamedAli-zmhf]

thanks a lot for your effort, but I had a question, if I add values to the dimension tables (in source workbook and saved it then exit it), and go to the destination workbook (with the data model ) and clicked UPDATE, will it update or the tables workbook must be opened ?