Summary  
This chapter covers structuring an end-to-end data modeling and reporting workflow by preparing normalized tables, defining relationships, implementing aggregation measures, building a question-driven dashboard, and conducting a reliability/clarity/efficiency/readiness review.

General domain of usage  
Sales analysis

The capstone project brings together every skill covered in this course — data preparation, model building, DAX measures, and dashboard design — in a single end-to-end deliverable. The goal is not a perfect result. The goal is to demonstrate that you can take a business question, build a model that answers it, and explain the choices you made.
## Task

Excel Workbook

You can work with any of the following: the Nordic Bikes data used throughout the course (recommended if you want to focus on building without learning new data), the provided *6.3 – Alternate_FlatSheet* for Softworks Solutions — a fictional B2B software company with 18 months of orders across four offices, two segments, and ten products (recommended if you want a fresh challenge) — or your own data from work (recommended if you want the most direct practical relevance).

Note

#### Step 1 — Define the business question 

1. Write one clear business question that your model will answer. 
2. Identify who would use the report and what decision it supports. 

**Examples for the alternative dataset**: Which office generates the most revenue? Which product type has the highest average order value? How does monthly billing revenue compare to one-off service revenue over time? 

#### Step 2 — Prepare the data 

1. Open your chosen dataset and inspect the structure. 
2. Identify the fact table and dimension tables. Define the grain of the fact table: what does one row represent? 
3. Split the flat data into separate tables. Each table should do one job. 
4. Create a Dates table covering the full date range of your data, plus additional future periods. 
5. Name all tables and columns clearly before loading anything into Power Pivot. 

#### Step 3 — Build the data model 
1. Load the tables into Power Pivot. 
2. Create the relationships between fact and dimension tables and validate them in Diagram View. 
3. Test the relationships with a simple PivotTable before moving on. For example, total revenue by Office or by ProductType. 

#### Step 4 — Write the measures 

1. Write at least three DAX measures that answer different aspects of your business question. 
2. Test each measure in a PivotTable before using it in the dashboard. 

Suggested measures for the alternative dataset: Total Revenue, Number of Orders, Average Order Value, Revenue by BillingModel. 

#### Step 5 — Build the dashboard 

1. Create a Dashboard sheet with PivotCharts, PivotTables, and at least two slicers. 
2. Each visual should answer one question. Avoid duplicating the same insight in multiple charts. 
3. Keep the layout clean. The Dashboard should be usable by someone who did not build it. 

#### Step 6 — Review the model 

Before finishing, evaluate the workbook using the four lenses from Section 6.1. 
- **Reliability** — Does the workbook return consistent results under different slicer combinations? 
- **Clarity** — Could another person identify the data sheets, model, and dashboard within a minute? 
- **Efficiency** — Are there columns or tables that add weight without contributing to the reports? 
- **Readiness** — Could someone else refresh this workbook next month without rebuilding it?

Learn how to turn raw Excel data into a connected data model with Power Pivot, DAX measures, and an interactive dashboard that updates automatically. Starting from a flat table, you will build relationships between multiple tables, write calculated metrics, and deliver a polished, reusable report — all inside Excel.
Suitable for Excel users with basic experience who want to move beyond flat-sheet reporting.

An introduction to what data modeling is, why it matters, and what you will build throughout the course. You will meet the Nordic Bikes project and see the finished dashboard before writing a single formula.

Before building a model, your data needs the right structure. This section covers how to split flat data into clean, purpose-built tables with proper keys, consistent formatting, and clear separation between facts and dimensions.

The core technical section of the course. You will enable Power Pivot, load your tables into the data model, connect them with relationships, and learn how to spot and fix the problems that break a model silently.

Learn to write calculations that go far beyond what Excel formulas can do alone. From basic KPI measures to time intelligence functions, this section builds the analytical layer that powers your reporting.

Turn your model into a finished product. You will build pivot tables that answer real business questions, convert them into charts, and assemble everything into an interactive dashboard controlled by shared slicers.

A structured review of what makes a model reliable, clear, and ready to hand over. The section closes with a capstone project where you apply every skill from the course end to end.

Capstone Project and Reflection

Task

Step 1 — Define the business question

Step 2 — Prepare the data

Step 3 — Build the data model

Step 4 — Write the measures

Step 5 — Build the dashboard

Step 6 — Review the model