PivotTables, the Data Model, and Intro DAX (Power Pivot)

💡 Key concepts

• PivotTable power features: Show Values As (% of Parent, Running Total, Difference From), date grouping, slicers connected to multiple pivots, timelines, GETPIVOTDATA for report layouts.
• Data Model: Load tables with "Add this data to the Data Model". Relationships replace dozens of XLOOKUP columns. It's the same engine as Power BI (VertiPaq).
• Relationship basics: One-to-many from lookup (dimension) tables like Programs to transaction (fact) tables like EVM and Budget.
• Measures vs. calculated fields: Data Model measures are written in DAX:

  Total ACWP := SUM(EVM[ACWP])
  CPI := DIVIDE(SUM(EVM[BCWP]), SUM(EVM[ACWP]))
  Obligation Rate := DIVIDE(SUM(Budget[ActualObligation]), SUM(Budget[PlannedObligation]))
  

• CUBE functions (optional): CUBEVALUE for free-form report layouts driven by the model.

🛠 Hands-on exercise: Multi-Table Portfolio Pivot

Data: programs, evm_monthly, budget_execution, contracts.

1. Load all four to the Data Model through Power Query (connection only + Data Model).
2. In Diagram View, relate Programs[ProgramID] → EVM[ProgramID], Budget[ProgramID], Contracts[ProgramID].
3. Create measures: Total BCWP, Total ACWP, Total BCWS, CPI, SPI, CV, SV, Planned Obligation, Actual Obligation, Obligation Rate.
4. Build a PivotTable: Portfolio → Program on rows, with CPI, SPI, and Obligation Rate. Add slicers (Portfolio, ACAT) and a timeline on Budget[CalendarMonth].
5. Notice the timeline only filters Budget. Write down why. (That's the date-table problem, which gets solved properly in Module 2.3.)

📦 Mini-project: Monthly Program Review Pivot Pack

A workbook with three connected pivots (EVM by program, obligations by appropriation, open risks by category) and shared slicers, plus a "print view" sheet using GETPIVOTDATA or CUBEVALUE to lay out a one-page summary in a fixed format.