Summary
Keywords
Full Transcript
Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/AllFilesBI348Analytics.htm Learn about PowerPivot & Data Model in Excel 2016. 1) (00:11) Intro to what we will do in this video 2) (00:30) Step 1 for Building Data Model: Get Data into PowerPivot. For our example we convert Proper Data Sets in Excel to a Table and use “Add to Data Model” button in PowerPivot Ribbon Tab 3) (01:00) Excel 2016: New PowerPivot Ribbon Tab. They have renamed “Calculated Field” Button (Excel 2013) to “Measure” button (Excel 2016). We can build DAX Measures (Calculated Fields) with this new button. 4) (02:24) Step 2 for Building Data Model: Create Relationships between related tables using the “Diagram View” button in the “Manage Data Model” window. 5) (02:47) Excel 2016: New one-to-many visual presentation in the Diagram View window. We can see a line with the number one ( 1 ) next to the primary key field in the lookup table (dimension table) and an asterisk next to the Foreign Key in the Fact table. 6) (03:34) Step 3 for Building Data Model: Build DAX Calculated Column formulas. In this example we calculate Net Revenue for each transaction using the RELATED DAX function and the ROUND DAX function and a number of columns from the Fact Table. We also learn about the convention for adding columns to DAX formulas: ALWAYS use the Table Name and the Field Name (Column Name) with square brackets around it. 7) (05:56) Discussion of Implicit vs. Explicit formulas. 8) (05:56) Step 3 for Building Data Model: Build DAX Measures (Calculated Fields) formulas. In this example we will calculate Total Net Revenue using the DAX SUM function and the Calculated Column from our Fact Table. This is an explicit formula that can work efficiently with the Columnar database on big data sets. 9) (07:17) Add Number Formatting to Measure (Calculated Field) 10) (07:32) Discussion of Filter Context: The ability of the Measure (Calculate Field) to respect criteria in the PivotTable and filter the underlying table to result in a range that is smaller than the full table size and will contribute to faster formula calculation time; in essence the DAX formula and the Columnar Database work together efficiently – and much more efficiently than normal PivotTable calculations or normal spreadsheet Excel formulas. 11) (08:00) Build PivotTable from Data Model 12) (08:30) Excel 2016: New “F of X” Function Icon for Measures (Calculated Fields) in the PivotTable Field List. 13) (09:23) More discussion of Filter Context and why DAX Measures (Calculated Fields) can calculate efficiently on Big Data. 14) (09:59) Summary and Conclusion Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video. Introduction to PowerPivot and Data Model in Excel 2016, What is PowerPivot Excel 2016? What is Data Model Excel 2016? What is New in Excel 2016 PowerPivot? What is new in Excel 2016 Data Model?
Continue this lesson in the app
Install CourseHive on Android or iOS to keep learning while you move.
