Power Pivot — Excel Course

Create relationships and a reusable data model. Learn it step by step with guided practice on Excelquro.

Understand

The Sales table is the fact table containing measurable events. Customers, Products, Branches and Dates are dimensions that describe those events. A one-to-many relationship usually runs from a unique dimension key to repeated fact keys. A star schema reduces repeated descriptive data in the fact table.

Worked example

Once the relationship exists, a PivotTable can summarize Sales by Branch without copying Branch into every transaction calculation formula.

Sales[Amount] related to Branches[BranchID]

Follow along

  1. Identify the fact table and its measurable fields.
  2. Identify dimensions and confirm their keys are unique.
  3. Create one-to-many relationships from dimensions to Sales.
  4. Build a PivotTable using a dimension field and a fact measure.
  5. Filter the dimension and observe the fact result change.
  6. Check for orphan keys and duplicate dimension keys.

Interactive practice runs in your browser.