Power BI Case Study
Sales Performance Dashboard
2024–2025
Raw multi-store retail sales data, cleaned and standardized with Power Query, modeled as a star schema, and calculated with 13 DAX measures — then presented as a two-page executive dashboard. This page rebuilds that report for the web, using the actual numbers queried from the model.
- 01ExtractExcel workbook, 4 sheets (lookups & 2 years of transaction data)
- 02TransformPower Query — rename, cast types, merge, calculated columns
- 03ModelStar schema — 1 fact table, 3 dimension tables
- 04Measure13 DAX measures — aggregation, time intelligence, Pareto
- 05Visualize2-page executive dashboard
Backend
Data Modelling & Data Engineering
The part a dashboard viewer never sees, but the part that determines whether the numbers can be trusted: the data source, the standardization process, the table relationships, and the DAX logic behind every metric.
1 Source Data & Standardization (Power Query / M)
All data comes from one Excel workbook. The file below has the assignment brief and an unused product
sheet stripped out, leaving the four sheets that actually feed the model:
STORE LOOKUP, Product Lookup, SALES DATA 2024, and
SALES DATA 2025. Each raw sheet has the usual problems of operational data: inconsistent
headers, mixed naming conventions, and a schema that changes between years.
STORE LOOKUP — cleaning up column names
| Source (raw) | Model | |
|---|---|---|
sitA_codA | → | site_code |
STORE NAME | → | Store Name |
City | → | City Name |
LOCATION CATEGORY | → | Location |
The source header even contains a typo (sitA_codA) — cleaned up into a
consistent snake_case / Proper Case naming convention across the model.
SALES DATA 2024 vs 2025 — mismatched schemas
The 2025 sheet has an extra pos_number column (POS transaction id) that
the 2024 sheet doesn't have — a sign the store's POS system was upgraded partway
through the period. Before merging, both sheets were standardized to the same schema:
- Renamed
Store Name/Price/Qty/Revenue→store_name/price/qty/revenue - Added a calculated
Year = Date.Year([order_date])column - Combined both years into one fact table with
Table.Combine()
Source = Table.Combine({#"SALES DATA 2024", #"SALES DATA 2025"})Because Table.Combine merges by column name, 2024 rows automatically get
a blank pos_number — so the Total Transaction measure is most representative
for the period since the new POS went live (2025).
2 Model Structure — Star Schema
The Sales Data fact table (126,111 rows) sits at the center, connected to three dimension
tables through one-directional many-to-one relationships — a classic star schema that keeps DAX fast and
filter context predictable. There's also a _Measures table, deliberately left empty of data
columns (a disconnected table), that exists purely as a home for every measure so the field list stays
clean and separate from the raw data.
| From | To | Cardinality | Filter direction |
|---|---|---|---|
Sales Data[order_date] | Date Table[Date] | Many → One | Single |
Sales Data[site_code] | STORE LOOKUP[site_code] | Many → One | Single |
Sales Data[article_code] | Product Lookup[article_code] | Many → One | Single |
3 Calculated Columns — Enrichment at the Model Layer
Two important columns don't come from the source data — they were added deliberately at the model layer to support sharper business analysis:
Custom Date Table
Built by hand (instead of Power BI's built-in auto date/time) so month sorting and Year/Month stay consistent for time intelligence:
Date Table = CALENDAR(DATE(2024,1,1), DATE(2025,12,31))
Year = YEAR('Date Table'[Date])
Month Number = MONTH('Date Table'[Date])
Month Name = FORMAT('Date Table'[Date], "MMMM")City Category — business segmentation
A business column for comparing store performance inside vs. outside the home city (Depok):
City Category =
IF('STORE LOOKUP'[City Name] = "Depok", "Inside", "Outside")Used directly in the slicer & the "Revenue per City Category" chart on the dashboard.
Backend
13 DAX Measures
Built in layers: basic aggregation → time intelligence for growth → cumulative ranking for Pareto analysis.
All measures live in the disconnected _Measures table.
Frontend
Dashboard — Web Recreation
The first two pages of the original report, rebuilt for the web using the actual numbers queried from the model above (not placeholder data). The filters on the left are interactive — try clicking them.