MEGA PROJECT 1: Building a Sales Analytics Dashboard From Scratch - Model, Measures, and Stakeholder Design

Introduction

Theory without application is trivia. You've learned data sources, Power Query, modeling, star schemas, relationships, date tables, columns vs measures, context transition, and validation. Now you bring it all together in one end-to-end project.

This is not a tutorial where you replicate someone else's dashboard click by click. This is a project methodology: how to take a business question, design a data model, build measures, create visuals, validate results, and publish - the way it happens in actual analytics teams.

This is your first portfolio piece. Build it like your next employer is watching.

---

Core Concept: The Project Methodology

Every real Power BI project follows five phases:

Phase 1 - Requirements gathering: What questions does the business need answered? Who will use this report? What decisions will they make from it?

Phase 2 - Data modeling: Design the star schema. Identify fact and dimension tables. Build relationships. Create the Date table.

Phase 3 - Measure development: Write the DAX measures that answer the business questions. Validate each against source data.

Phase 4 - Visual design: Build report pages that prioritise the user's primary question. Use visual hierarchy (KPIs at top, details below, drill-through for depth).

Phase 5 - Validation and publishing: Reconcile against source of truth. Test slicers. Check mobile layout. Publish to Service.

Most tutorials skip phases 1, 3 (validation part), and 5. This is why most tutorial-built dashboards don't survive contact with real stakeholders.

---

Real-World Application: The Brief

Business context: NovaMart is a multi-region retail company with 12 stores across 4 regions. The VP of Sales needs a monthly dashboard answering three questions:

1. "Are we hitting our revenue targets?"
2. "Which regions and products are driving growth or decline?"
3. "Where should I focus next quarter?"

These three questions drive every design decision - model, measures, and visuals.

---

Technical Deep Dive: The Build

Star Schema

[DimDate] ──→ [FactSales] ←── [DimProduct]
                   ↑
[DimStore] ────────┘
[DimCustomer] ─────┘

FactSales: OrderID, DateKey, ProductKey, StoreKey, CustomerKey, Quantity, UnitPrice, Discount, Revenue
DimDate: Date, Year, Quarter, Month, DayOfWeek, IsWeekend, FiscalYear
DimProduct: ProductKey, ProductName, Category, SubCategory, Brand, CostPrice
DimStore: StoreKey, StoreName, Region, City, Format (Express/Superstore)
DimCustomer: CustomerKey, CustomerName, Segment (B2B/B2C), JoinDate

Core Measures

// Revenue KPIs
Total Revenue = SUM(FactSales[Revenue])
Avg Order Value = DIVIDE([Total Revenue], DISTINCTCOUNT(FactSales[OrderID]))
Gross Profit = [Total Revenue] - SUMX(FactSales, FactSales[Quantity] * RELATED(DimProduct[CostPrice]))
Gross Margin % = DIVIDE([Gross Profit], [Total Revenue])

// Time Intelligence
Revenue YTD = TOTALYTD([Total Revenue], DimDate[Date])
Revenue vs LY = [Total Revenue] - CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DimDate[Date]))
Revenue Growth % = DIVIDE([Revenue vs LY], CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DimDate[Date])))

// Operational
Transactions = DISTINCTCOUNT(FactSales[OrderID])
Items per Transaction = DIVIDE(SUM(FactSales[Quantity]), [Transactions])
Active Customers = DISTINCTCOUNT(FactSales[CustomerKey])

Report Layout (3 Pages)

Page 1 - Executive Overview

• 4 KPI cards at top: Revenue, Margin %, YoY Growth %, Active Customers
• Revenue trend line chart (by month, with LY overlay)
• Revenue by Region bar chart
• Top 5 Products table

Page 2 - Regional Deep Dive

• Region slicer
• Store-level performance matrix (Store, Revenue, Growth %, Margin %)
• Category treemap
• Monthly trend for selected region

Page 3 - Product Analysis

• Category/SubCategory drill-through
• Product scatter plot (Revenue vs Margin %)
• Slow-moving products table (Revenue declining > 10% YoY)

Validation

Before publishing, validate:

1. Grand total Revenue vs source system (SQL query)
2. Revenue by Region vs source (GROUP BY region)
3. Transaction count vs source (COUNT DISTINCT order_id)
4. Spot-check 3 individual orders (drill to row level)

---

Common Mistakes

Starting with visuals. Build the model and measures first. Validate them in a simple matrix. Only then design the visual layout. Building visuals on unvalidated data means rebuilding them after corrections.

Showing every metric on one page. A VP needs 4-6 KPIs. A regional manager needs store-level detail. Don't put both on the same page. Use separate pages with slicers and drill-through.

No comparative context. A revenue number means nothing without comparison. "Revenue is $1.2M" is data. "Revenue is $1.2M, up 8% YoY, and 3% above target" is insight. Always include at least one comparison: vs last year, vs target, vs budget.

Forgetting mobile layout. In Model view, your report will look great on a desktop monitor. But if the VP checks it on their phone during a commute, the default layout will be unreadable. Build a dedicated mobile layout for at least the executive overview page.

---

Pro Tips / Industry Insight

Stakeholder iteration loop: Show the VP a wireframe (even a paper sketch) before building anything. Get sign-off on which questions the dashboard answers. Build a draft with real data. Review with the VP. Iterate. This loop - wireframe → build → review → iterate - is how professional dashboards are delivered. It prevents the "this isn't what I asked for" conversation.

The portfolio version: When publishing this project to your portfolio, add a README that explains:

1. The business question it answers
2. The data model design decisions
3. One interesting finding from the data
4. A link to the published report

Employers don't evaluate dashboards on aesthetics. They evaluate them on thinking - did this person understand the business problem, model the data correctly, and produce actionable insight?

---

Summary

The mega project brings together every concept from Season 1: data import, Power Query cleaning, star schema design, Date table, measures, context transition, and validation. The methodology is: requirements → model → measures → visuals → validation → publish. Start with business questions, not visuals. Validate before publishing. Include comparative context in every KPI. Build for mobile. This is your first portfolio piece - build it like your next employer is watching.

Build your skills and track your progress with a live DRI score at devwithdata.in.