digest
Short Summary
Main recommendation: To analyze related Excel data at scale, convert each data range into an official Excel table, add those tables to the Power Pivot Data Model, define one-to-many relationships, and then build calculated columns and explicit DAX measures that feed a pivot table. The video demonstrates this workflow in Excel 2016 using a sales fact table and two lookup (dimension) tables, producing a Total Net Revenue report by region and product.
Key Points
- Data must be an official Excel table (Ctrl+T) before it can be added to Power Pivot; tables are named with a convention such as F for fact tables (e.g., F Sales).
- Relationships are built in Diagram View by dragging the lookup column (the unique list, marked 1) onto the matching column in the fact table (marked with an asterisk, representing many).
- A calculated column (Net Revenue) uses the RELATED function to pull the lookup price, then multiplies by (1 minus discount) and units, rounded to the nearest penny using ROUND(...,-2).
- An explicit measure (Total Net Revenue) is written as SUM over the calculated column; the video notes that measures respect pivot table filters and are faster on big data than implicit calculations.
- Number formatting applied in the Data Model carries into any pivot table, so it does not need to be reapplied each time.
Details Worth Noting
- Concrete instructions:
- Select a cell in each data range and press Ctrl+T to create a table; in Table Design, set the Properties name (e.g., F Sales for the fact table, D Products and D Sales Rep for dimension tables).
- Select a single cell in each table and click Add to Data Model (Power Pivot tab) to load it into the Data Model; the table appears with a link back to the Excel table.
- Go to Diagram View and drag the lookup key (Products in D Products, Sales Rep in D Sales Rep) onto the matching field in F Sales to create one-to-many relationships.
- In Data View, double-click the empty column header, name it Net Revenue, and enter =RELATED(D Products[Price]) * (1 - F Sales[Discount]) * F Sales[Units] wrapped in ROUND(..., -2) so it fills down automatically.
- Create the measure by typing "Total Net Revenue:=SUM(F Sales[Net Revenue])" in the measure grid, then apply US dollar number formatting.
- Click Pivot Table from the Manage Data Model window, place it on a new sheet, drag Region from the Sales Rep table to Rows, Products to Rows, and Total Net Revenue to Values.
- Set Design > Report Layout > Show in Tabular to display the pivot table in tabular form.
- The video's example total for the fact table is $905,702 plus pennies.
- The speaker points out that formulas referencing a column should use table name and field name (e.g., F Sales[Discount]); square brackets alone are used only for measures or calculated fields referenced from other formulas.
- The workbook is Highline BI 348 Chapter 2.75 Example 1, and this video is part 41 of the series.
- In Excel 2016 the feature is called Measures; it was called Measures in earlier versions, then renamed Calculated Fields in 2013, and changed back to Measures in 2016.
Bottom Line
Loading tables into the Data Model with proper relationships, then using RELATED for calculated columns and SUM-based measures, lets a single pivot table combine data from several tables with fast, filter-aware calculations.
