Skip to content
All projects

Project 03 // Analytics · Aug 2025

Sales Planning Analysis

Who is on plan, and where targets are at risk

Bars fill toward each district’s 2022 plan; the chain reached 5.90 billion VND of a 6.43 billion plan, 91.7%.
of the 2022 plan reached, chain-wide
91.7%
Order lines
58K+
Fact tables
2
Shared dimensions
6

In short

The question

Which stores, regions, and product groups are meeting their monthly plan, and which are falling behind? And how is performance trending year over year and over the last 12 months?

What I built

  1. 01
    SQL Server
    • SQL Server
  2. 02
    Power Query
    • Power Query
  3. 03
    Galaxy schema
    • Galaxy-schema modeling
  4. 04
    DAX measuresPlan completion, year-over-year and rolling 12 months
    • DAX time intelligence
  5. 05
    Power BI reportDrill-through from region to store
    • Executive dashboard design

What it found

District plan completion, 2020–2022
  • Lowest district~89%
  • Highest district~98%

Plan

No district fully hit its target.

  • District plan completion ranges from about 89% to 98%: no district fully hit its target over 2020–2022.
  • Over 36 months, year-over-year revenue ranged from +37% to −15%; this project describes the swings and does not test their causes.
  • Plan completion % = Actual ÷ Plan at any grain: store, region, product group, month.
  • Power BI
  • DAX
  • SQL Server
  • Galaxy schema

Full case study // tap a section to open it

01Problem & context

Which stores, regions, and product groups are meeting their monthly plan, and which are falling behind? And how is performance trending year over year and over the last 12 months?

Retail managers need to know early which stores, regions, and product groups are going to miss their monthly targets, so they can act before the month closes rather than explain the miss afterwards.

A multi-page Power BI solution on a synthetic dataset for the KPIM Mart retail chain that gives managers full visibility into sales performance, plan attainment, and growth trends.

Sales and monthly plan data from SQL Server are modeled as a galaxy schema: two fact tables, actual sales and plan targets, sharing conformed dimensions, so the two can be compared at any grain.

DAX measures calculate plan completion, year-over-year growth, year-to-date comparisons, and rolling 12-month trends by store, product group, and region, and drill-through takes a manager from a summary number to the transactions behind it.

02My role & approach

Solo project: data preparation, the galaxy-schema model, DAX measures, and report design.

  1. 01

    Integrated sales and monthly plan data from SQL Server into one Power BI model.

  2. 02

    Modeled a galaxy schema: two fact tables share conformed dimensions, so actual and planned figures can be compared at any grain.

  3. 03

    Wrote DAX measures for plan completion, year-over-year growth, rolling 12-month trend, and store and region rankings.

  4. 04

    Designed KPI cards, an actual-vs-plan trend, rankings, breakdowns, and drill-through to store detail.

Problems I hit and how I handled them

  • Actuals are recorded per transaction line while plans are monthly targets. A galaxy schema with two fact tables on shared dimensions lets them be compared at any grain.
  • Time intelligence in DAX: plan completion, year-over-year growth, and a rolling 12-month trend that hold up under every slicer.
  • Turning a wide dataset into a report a manager can use: KPI cards first, then rankings, then drill-through to the store responsible.
03Tech stack & data model
  • Power BI Desktop

    Four report pages with cross-filtering, drill-through, slicers for year and promotion, and a decomposition tree.

  • DAX time intelligence

    Plan completion (actual ÷ plan), Sales YTD vs. last year with TOTALYTD and SAMEPERIODLASTYEAR, YoY growth, and rolling 12-month trends.

  • Power Query

    Type casting, renaming, null handling, date formatting, and merges to shape the source tables for the model.

  • SQL Server

    Source system for the sales transactions and plan tables.

  • Excel

    Data exploration and cross-checking DAX results against manual calculations.

  • Galaxy schema

    Two fact tables on shared dimensions, the right shape for actual-vs-plan analysis.

Data model

A standard star schema has one fact table. Comparing actuals with plans needs two, so both facts connect to the same date and store dimensions and one measure can divide one by the other.

  • Fact table

    fact_sales

    One row per order line (about 58K): order number and line, order date key, customer, product, promotion, and quantity.

  • Fact table

    fact_sales_plan

    Monthly sales targets by district, shared date and store dimensions with fact_sales.

  • Dimension

    dim_store

    Store, district, city, manager, and location, for regional and manager-level analysis.

  • Dimension

    dim_product

    Product, brand, category, price, and cost, for product-mix analysis.

  • Dimension

    dim_customer

    Demographics and loyalty group, for customer segmentation.

  • Dimension

    dim_promotion

    Promotion type, discount, and dates, to judge campaign effect.

  • Dimension

    dim_date

    Date, month, quarter, and year, powering every time-intelligence measure.

  • Measures

    _Measures

    Plan completion and the other KPI measures, kept separate from the data tables.

Skills shown

  • Galaxy-schema modeling
  • DAX time intelligence
  • Power Query
  • SQL Server
  • Executive dashboard design
04More results
  • Year-over-year and rolling 12-month views show whether a miss is a blip or a trend.
  • Drill-through takes a manager from a regional miss straight to the store responsible.
05What I learned & would improve

Lessons

  • Comparing monthly plans with transaction-level actuals needs two fact tables on shared dimensions; one flat table cannot answer both grains.
  • Managers act on the answer, not the chart: KPI first, then the ranking, then drill-through to the store responsible.

What I would do next

  • Add a forecast for the rest of the year so a district can see a miss coming.
  • Publish with row-level security so each manager sees only their own stores.

Try it // runs in your browser

Write your own code

Monthly actual and planned revenue by district from this project (a synthetic dataset; 289 district-month rows, in Vietnamese dong). Edit the code and run it.

Table sales: district, month, actual, plan

Ctrl + EnterLoading data...

Results appear here (first 10 rows). In Python, the last expression is shown, and print() works too.

Explore the report

Rebuilt from the project's data so it opens in any browser, no login. Use the filters and every visual recalculates.

Power BIThanh VuSales Planning Analysis
Export .pbixReading view
Year

–

Actual revenue

–

Plan

–

Plan completion (actual ÷ plan)

Monthly actual revenue vs. plan
Plan completion by district

Microsoft Power BI1 of 6

See it in Power BI

The real report in Power BI Desktop. The subtitles explain each click.

Power BI Desktop // subtitles on // tap a step to jump

01 // District revenue plan

02 // Category table

03 // Store & manager overview

04 // Manager achievement

Walkthrough

What each dashboard and diagram shows, and why it matters. Click an image to enlarge it.

  1. 1

    Page 1: Sales analysis

    The executive overview: revenue against last year and against plan, where sales come from, and who is on track.

    • Two KPI cards answer the first questions: are we growing versus last year, and are we hitting plan, with a check or warning icon on each.
    • Donut charts split revenue by district and by product group.
    • A monthly trend toggles between overall, versus plan, and versus last year.
    • The manager table shows plan attainment and growth per manager with sparklines.
  2. 2

    Page 2: Drill-through by manager

    Right-click any number to land here: the month and year-to-date against the prior year, by store manager.

    • In this drill-through view, December 2022 sales of 1,858,396,200 VND are up 60% on December 2021.
    • Year to date is down 15% on the prior year, a nuance the monthly number alone would hide.
    • Bars show each manager's YTD against last year, colored red for decline and blue for growth.
  3. 3

    Page 3: Decomposition tree

    Self-service root-cause analysis: start from year-to-date revenue and expand by store, brand, loyalty group, income, and promotion.

    • Managers can follow a revenue figure down to the customer segment and promotion driving it.
    • Filter chips show the current path and can be removed to widen the view.
  4. 4

    Page 4: Transaction-level detail

    The audit trail behind every KPI: order, ship, and payment dates, store, customer, quantities, discounts, and sales.

    • Drill-through destinations land here so any summary number can be traced to its orders.
    • Footer totals update with the year and promotion filters.
  5. 5

    Galaxy schema in Power BI

    Both fact tables connect to shared dimensions, which is what makes actual-vs-plan measures possible.

    • Plan completion = DIVIDE([Total Sales], [Sales Plan]) pulls from both facts in one measure.
    • A dedicated measures table keeps calculations organized.
Next projectLancaster County Economic Indicators
Esc

↑ ↓ to moveEnter to open