Power Pivot Business Model
Build a small data model with branches and transactions, then define reusable measures.
Advanced · 60–90 min
Objectives
- Separate facts and dimensions
- Create relationships
- Build measures
- Analyze KPIs
Project dataset
| Row | A: Transaction ID | B: Branch Code | C: Collection | D: Target |
|---|---|---|---|---|
| 2 | T001 | MUM | 62000 | 50000 |
| 3 | T002 | PUN | 49000 | 55000 |
| 4 | T003 | MUM | 58000 | 60000 |
| 5 | T004 | NAS | 51000 | 45000 |
| 6 | T005 | MUM | 72000 | 65000 |
Guided checkpoints
Separate the tables
Use a Branch dimension table and a Transactions fact table. Keep Branch Code as the common key.
Understand a simple data model structure.
Create the relationship
Create a one-to-many relationship from the Branch table to Transactions using Branch Code.
Connect related tables.
Create Total Collection
Create a reusable measure for total collection.
Introduce measures.
Create Achievement %
Create a measure that divides Total Collection by Total Target.
Build a KPI from measures.
Present the model
Use the model in a PivotTable with Branch as rows and your measures as values.
Turn a model into a report.
Download sample project .xlsx · All portfolio projects
Interactive practice runs in your browser.