Retail sales model — preparation and assignment structure
A sales dashboard where every number comes from a measure you write from scratch, with automatic color indication
for sales reps. Working on Retail_StarSchema_PowerBI.xlsx — a star schema with 11 tables,
36,607 rows and two Snowflake branches. 27 exercises, each on a separate page and with a full solution. Note: the download
and the practice are On the computer only — Power BI Desktop doesn't exist on phone.
The data is synthetic. The country and city names are real, but every customer, product, rep
and order in the file were artificially generated.
the model. The central fact table Fact_Sales at the core of a product row
in the order, and next to it Fact_Returns andFact_SalesTargets. Eight dimensions, two of them
branches Snowflake Deliberately: Dim_Geography → Dim_Customer and
Dim_ProductCategory → Dim_Product. 11 relationships, all one-to-many in direction
Single from the dimension to the fact. Mark the Dim_Date As a date table with
Full_Date.
Three points that will save you time. (1) Dim_Promotion including an item with the key
0 named No Promotion, so there won't be blank keys in the fact table. (2)
Fact_SalesTargets in a monthly grain while sales are daily — a join through
Month_Start_Date_ID. (3) Ship_Date It's a regular date column and not a key, so as not
to create a second relationship toDim_Date; anyone who wants to practice a role-playing dimension can add an inactive relationship
and activate it with USERELATIONSHIP.
Assignment structure
Part 1
14 DAX measures
Ten basic measures — SUM, DISTINCTCOUNT, DIVIDE, two CALCULATE, ALL, IF and two SWITCH — and three window functions: RANK, INDEX and WINDOW.
Part 2
Two dashboard pages
Four cards on a tray, a bar chart, a line chart, a year slicer, a reps table, Top 10 products, a Matrix, and a combo chart.
Part 3
Double conditional formatting
The same three-tier split in two techniques: Rules inside a single visual, and a SWITCH measure that feeds the color to every visual.
All the numbers were verified in pandas against the file
1.1 · Total Sales — the base measure
Create a measure named Total Sales that summarizes the Fact_Sales[Net_Amount]. Format as currency with a thousands separator.
Total Sales = SUM ( Fact_Sales[Net_Amount] )
creationRight-click on Fact_Sales ← New measure
designFormat ← Currency ← 0 digits after the decimal point
Why Net and not Gross:Gross_Amount it's before discount
(€67,937,751), Net_Amount after (€62,929,759). The difference —
€5,007,992 — is the discount given in promotions. Both numbers are correct, they answer two different
questions, so you must define the measure before arguing about a number.
and what about returns: €2,364,425 were refunded to customers
(3.76% of sales), and they sit inFact_Returns — are not deducted from this measure.
The net-net sales are €60,565,334. This is an excellent bonus exercise.
result: €62,929,759 — the anchor number for the whole assignment.
1.2 · Total Orders — how many orders, really
Create a measure named Total Orders that counts orders uniqueness based on Fact_Sales[Order_Number].
Total Orders = DISTINCTCOUNT ( Fact_Sales[Order_Number] )
Note the column:Order_Number and not
Sales_Line_ID. An order with multiple items appears in multiple rows, so
COUNT It would return 30,000 — rows, not orders. The average is
2.16 rows per order.
result: 13,892 orders out of 30,000 sales rows.
1.3 · Avg Order Value — the Lego principle
Create a measure named Avg Order Value — strictly from the two existing measures, without the division sign / and without AVERAGE. Format to two decimal places.
Avg Order Value = DIVIDE ( [Total Sales], [Total Orders] )
The average of what:AVERAGE(Fact_Sales[Net_Amount]) would
return the average per order line, not per order. When the measure is built from two existing measures, the denominator is defined
explicitly. DIVIDE Preferable to / because it returns blank instead of an error when the denominator is zero.
The measure rolls automatically: Drag it onto the reps table and it will return
the average basket of each rep — see the insight in exercise 2.4.
result: €4,530 to the order.
1.4 · Total Profit — the second measure
Create a measure named Total Profit that summarizes the Fact_Sales[Gross_Profit]. Bonus: measure Profit Margin % — DIVIDE of profit by sales, as a percentage.
Total Profit = SUM ( Fact_Sales[Gross_Profit] )
Profit Margin % = DIVIDE ( [Total Profit], [Total Sales] )
The column is derived, not random:Gross_Profit =
Net_Amount less COGS, in each of the 30,000 rows.
You can verify this in Power Query before starting.
255 rows with negative margin (0.85%) —
a 25% promotion that dropped below cost. This happens in real data, and it’s a good analysis question: which promotion ate
into the profit.
result: €25,282,189 · margin 40.18%.
1.5 · Digital Sales — first CALCULATE
Create a measure named Digital Sales that returns sales in digital channels only, with CALCULATE and a condition on Dim_Channel[Is_Digital].
What happens here: CALCULATE adds the condition to the filter bubble and only then
runs the [Total Sales]. The filter sits on a text column in the dimension table and flows through the relationship
to the 30,000 fact rows — without any JOIN.
The breakdown by channel: Mobile App €13,038,227 · Retail Branch €12,512,458 · Web Store €12,493,313 · Telesales €12,491,849 · Partner Reseller €12,393,913
result: €25,531,540 — 40.57% of sales.
1.6 · Promotion Sales — CALCULATE on the promotions dimension
Create a measure named Promotion Sales that returns the sales made on promotion. The trick: the dimension includes a member named Standard Price that represents a sale without a promotion — filter it out.
Why there is a "no promotion" member: Without it there would be
15,990 rows with an empty key, and every visual would show
(Blank). A default row with key 0 is the standard solution — and that's why in this file
there are 0 empty keys.
The discount given in promotions: €5,007,992 — almost the entire discount
in the file. Worth building a measure Discount Rate % and see which channel exploits it.
result: €26,793,724 — 42.58% of sales, in 14,010 rows.
1.7 · % of All Sales — ALL releases the filter
Create a measure named % of All Sales: Divide the [Total Sales] in total — a CALCULATE that removes the filter fromDim_SalesRep using ALL. Format as a percentage, two digits.
% of All Sales =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], ALL ( Dim_SalesRep ) )
)
What ALL does: The numerator lives in the row's bubble (one rep), and the denominator
is released from filtering and therefore always holds the entire company. In the Total row the numerator equals the denominator — and that's the check
that the measure was written correctly.
Result: the top representative holds 3.01% · The Total row shows 100.00%.
1.8 · Rep Status — IF vs. the average
Create a measure named Rep Status that returns text: "Above average" if the rep exceeded 1,570,000 euros, and otherwise "Below average".
Rep Status =
IF ( [Total Sales] >= 1570000, "Above average", "Below average" )
Where does 1,570,000 come from: €62,929,759 ÷ 40 reps =
€1,573,244, rounded. The measure returns text, so it fits a table, not a numeric card.
result: 20 Reps "above average" · 20 "below" — an almost perfect split in two.
1.9 · Rep Tier — SWITCH for three tiers
Create a measure named Rep Tier: "Top" from 1,700,000 and up, "Middle" from 1,470,000 and up, otherwise "Bottom". Use the template SWITCH ( TRUE (), … ).
Order determines: SWITCH stops at the first condition that holds. If the lower condition
is written first, the entire top tier will be classified as "middle", and the top tier will remain empty.
result: 9 "top" · 21 "middle" · 10 "bottom".
1.10 · Tier Color — a SWITCH that returns a color
Create a measure named Tier Color That returns HEX based on [Rep Tier]: top #132449, middle #F5C518, default #A9B4CC.
The two forms of SWITCH: In the previous exercise TRUE() because the conditions are
range comparisons; here the expression is a text measure and the values are an exact match.
the pitfall: SWITCH compares strings with an exact match. A single trailing space
in a string will fail silently and all rows will get the default. Copy the strings from the previous measure.
The measure isn't shown in any visual — it's used only for the conditional formatting in exercise 3.2.
1.11 · Rep Rank — RANK
Create a measure named Rep Rank that ranks the 40 reps by [Total Sales] Using RANK on ALL ( Dim_SalesRep[SalesRep_Name] ), with ORDERBY descending.
What ALL does: removes the row filter and returns all 40 reps
to the list of candidates.
What's different from RANKX: The default of ORDERBY is ASC, and therefore
without DESC The weak rep will get place 1. In the Total row the function returns blank.
result: Kenji Smith = 1 · Piotr Levi = 40.
1.12 · Leader Sales and % of Leader — INDEX
Create a measure named Leader Sales that returns, in every row, the sales of the first rep — CALCULATE with INDEX ( 1, … ). bonus:% of Leader.
A fixed money column — the leader's value repeats on every row
representative
Total Sales
Leader Sales
% of Leader
Kenji Smith
€1,896,727
€1,896,727
100.0%
Jan Ferrari
€1,817,937
€1,896,727
95.8%
Anna Bernard
€1,814,008
€1,896,727
95.6%
Sofia Muller
€1,777,435
€1,896,727
93.7%
Piotr Levi
€1,241,220
€1,896,727
65.4%
How to explain: RANK answers "where am I"; INDEX answers "show me who
is in position 1". Wrapping it in CALCULATE is what makes the measure take into account name instead here.
result: €1,896,727 in every row · the last one stands at 65.4% of it.
1.13 · Running Sales — WINDOW
Create a measure named Running Sales — a running total, using WINDOW ( 1, ABS, 0, REL, … ) With the same ALL and the same ORDERBY.
The logic of the four numbers:1, ABS = head of the list ·
0, REL = they stop at me. If you replace with1, ABS ← 3, ABS You'll get a fixed column
with the sum of the top three — one word turns a moving window into a fixed window.
A built-in sanity check: the last row must be exactly €62,929,759.
1.14 · Target Attainment — the third fact table
Create a measure named Total Target that summarizes the Fact_SalesTargets[Target_Amount], then Target Attainment % — sales divided by target.
Total Target = SUM ( Fact_SalesTargets[Target_Amount] )
Target Attainment % = DIVIDE ( [Total Sales], [Total Target] )
Core differences — the main takeaway here: Daily sales and monthly
targets. Both tables are connected toDim_Date in different columns
(Date_ID against Month_Start_Date_ID), so filtering a single day will show
sales without a target. Filter by month or year, not by day.
A third measure for a bonus:Total Refunds =
SUM ( Fact_Returns[Refund_Amount] ) — €2,364,425, which is
3.76% of sales. Three fact tables under the same slicers.
Result: target €63,014,903 · meeting the target 99.86%.
2.1 · Background, title, and KPI tray
Page 1 of the dashboard — background, title, and a tray the cards will sit on.
shadesOnly three: navy #132449, gold #F5C518 and neutral gray
backgroundClick on an empty canvas ← Format your report page ← Canvas background ← a lighter version of the naive ← Transparency = 0%
titleInsert ← Text box ← "the state of sales room"
trayInsert ← Shapes ← Rectangle ← horizontal strip ← Rounded corners ← Send to back
The shape must sit below to the cards.
2.2 · Images — search and add
Two image files will turn the report from an assignment into one that looks professional.
searchShopping cart or store on a transparent background — add transparent PNG and filter byTools ← Color ← Transparent
sizeWidth at least 300px
additionInsert ← Image ← the corner of the title, about 60 pixels tall
ratioFormat image ← Scaling = Fit, dragging from the corner only
Don't use the logo of a real retail chain — the data is fictional. A generic image from Unsplash or Pexels.
2.3 · The four cards
Build 4 cards above the tray. Style one fully, then duplicate it ×3 and swap the field.
Total Sales · Total Orders · Avg Order Value · Total Profit
The four cards and the verified values
card
the measure
The value to display
sales
Total Sales
€62,929,759
orders
Total Orders
13,892
average basket
Avg Order Value
€4,530
profit
Total Profit
€25,282,189
A display trap:Total Orders must
Display units = None, otherwise \"13.9K\" will be displayed. On the money cards, set
Currency with 0 digits so the four look uniform.
align: Ctrl+Click ← Group ← Distribute horizontally ← Align top.
2.4 · Bar chart — sales by rep
built Clustered column chart with Dim_SalesRep[SalesRep_Name] on the X axis and[Total Sales] On the Y axis. Descending sort, Data labels = On.
X-axis = Dim_SalesRep[SalesRep_Name]
Y-axis = [Total Sales] ← the measure, not the column
Six leaders and three trailing
#
representative
sales
% of the total
1
Kenji Smith
€1,896,727
3.01%
2
Jan Ferrari
€1,817,937
2.89%
3
Anna Bernard
€1,814,008
2.88%
4
Sofia Muller
€1,777,435
2.82%
5
Carlos Avraham
€1,771,693
2.82%
6
Carlos Peretz
€1,746,970
2.78%
38
Elena Mizrahi
€1,328,517
2.11%
39
Olivia Tanaka
€1,310,164
2.08%
40
Piotr Levi
€1,241,220
1.97%
Notice how tight the league is: Kenji Smith leads with
€1,896,727 and Piotr Levi closes out with €1,241,220 — a gap of
only 53% between first and last out of 40.
In a real dashboard this means ranking alone isn't enough to decide anything.
Now add the [Avg Order Value]: The leader stands at
€4,991 per order and the last one at €4,310. The difference in amount stems mainly from the number
of orders (380 vs. 288), not from basket size — the difference between a cumulative metric
and a ratio metric.
the leader: Kenji Smith — €1,896,727 (3.01%).
2.5 · Line Chart — Sales Trend
built Line chart with Dim_Date[Full_Date] on the X axis and[Total Sales] on the Y axis. Expand down to the Month level.
What we see: Three years almost completely flat — the gap between the
strongest and weakest year is less than 2%. The strongest month is 2025-07 with
€2,114,749, and the weakest is 2024-01 with €1,478,273.
Question for the class: Is the monthly volatility real seasonality or noise?
With only 36 months and this kind of spread — the answer is noise, and that's exactly what a student
needs to know how to say instead of inventing a story.
Record: 2025-07 — €2,114,749.
2.6 · Page 2 and the year slicer
Open a second page named "Rep and Product Spotlight" with the same background.
page＋ at the bottom ← Name: Reps & Products Spotlight ← same canvas background
slicerSlicer ← Dim_Date[Year] ← Style = Tile
designStretch across below the title · selected = dark, unselected = light
Check that the slicer affects all the visuals, including those built on Fact_Returns andFact_SalesTargets.
2.7 · Representatives Table — Where IF and SWITCH Live
built Table with Dim_SalesRep[SalesRep_Name] and the seven measures: Rep Rank, Total Sales, % of All Sales, % of Leader, Running Sales, Rep Status and Rep Tier. Descending sort, Totals = Off.
Dim_SalesRep[SalesRep_Name] · [Rep Rank] · [Total Sales]
[% of All Sales] · [% of Leader] · [Running Sales]
[Rep Status] · [Rep Tier]
Four leaders and two last
rating
representative
sales
% of the total
Status
Tier
1
Kenji Smith
€1,896,727
3.01%
Above average
top
2
Jan Ferrari
€1,817,937
2.89%
Above average
top
3
Anna Bernard
€1,814,008
2.88%
Above average
top
4
Sofia Muller
€1,777,435
2.82%
Above average
top
39
Olivia Tanaka
€1,310,164
2.08%
Below average
bottom
40
Piotr Levi
€1,241,220
1.97%
Below average
bottom
The difference between the two measures:Rep Status Compares to the average
computed from the data (€1,573,244), andRep Tier compares against fixed thresholds. A rep can
be "below average" and still be in the "middle" tier.
Why Totals = Off: Window functions return blank in the summary row.
20 \"above average\" · tiers: 9 / 21 / 10.
2.8 · Top 10 products — Bar with Top N
built Clustered bar chart with Dim_Product[Product_Name] on the Y axis and[Total Sales] On the X axis, and filter Top N = 10 By the measure.
Y-axis = Dim_Product[Product_Name]
X-axis = [Total Sales]
Filter = Top N, 10, by [Total Sales]
The top ten products
#
product
Subcategory
sales
1
Ardex Audio 1228
Audio
€583,483
2
Oryx Paper 1062
Paper
€527,442
3
Fernway Binder 1197
Binders
€514,267
4
Fernway Audio 1120
Audio
€502,029
5
Brightline Audio 1071
Audio
€501,447
6
Oryx Binder 1112
Binders
€477,644
7
Oryx Lighting 1026
Lighting
€471,020
8
Nexora Writing Tool 1098
Writing Tools
€470,305
9
Oryx Storage Unit 1269
Storage Units
€467,742
10
Volterra Laptop 1126
Laptops
€464,999
Note the long tail: 300 products in the catalog,
and the leader holds €583,483 — less than a percent of total sales. This is a spread-out catalog, not a catalog
that leans on a single hit. Drag the Dim_ProductCategory[Category_Group] to see where
the money is actually concentrated.
the leader: Ardex Audio 1228 — €583,483.
2.9 · Matrix — customer segment × channel
built Matrix with Dim_Customer[Segment] in rows, Dim_Channel[Channel_Name] in columns and[Total Sales] in values. Data bars = On.
Note the filter path: The segment comes fromDim_Customer
and the channel fromDim_Channel — two separate dimensions that intersect on the same fact table.
That's exactly what a star schema gives you for free.
Sum of all cells: €62,929,759 — identical to the base measure.
2.10 · Combo chart — sales vs. profit margins
built Line and clustered column chart with Dim_ProductCategory[Category_Group] on the X axis, [Total Sales] in columns and[Profit Margin %] on the line.
This is the insight of the whole dashboard: Technology sells the most
(43.6% of sales) but it's actually Office Supplies, the smallest of them
(19.8%), that is the most profitable — 41.5% profit margin versus
40.1%. Bars and volume tell one story, the line tells another.
Why two axes: Percent and euro don't share a scale. In a regular chart the percent line
would be squashed to zero — this is exactly the case where the rule "don't use two axes" doesn't apply.
The most profitable: Office Supplies — 41.5% profit margin.
2.11 · Navigation and polish
Add navigation buttons between the two pages and finish the polish.
openingChoose the chart ← Format visual ← Columns ← Colors ← fx
definitionFormat style = Rules · What field = [Total Sales]
rulesThree rules with New rule
criticalValue type = Number, not Percent
The result: 9 reps in dark navy, 21 in gold, 10 in light navy.
3.2 · Field value — the SWITCH measure feeds the color
Color the reps table (exercise 2.7) without writing a single rule — the color comes fromTier Color.
Format visual <- Cell elements
Series: Rep Tier
Background color = On <- fx
Format style = Field value
What field should we base this on? = [Tier Color]
Why this is stronger than Rules: The rules in exercise 3.1 live inside one
visual. The measure Tier Color It's a single source of truth — one change in SWITCH updates everywhere.
Both techniques produce exactly the same split: 9 / 21 / 10.
Summary — all the results in one place
A centralized verification table for all measures and every visual. Open it only after you've finished.
All verified results in one place
Measure / component
Verified result
Total Sales
€62,929,759
Gross before discount
€67,937,751 — discount €5,007,992
Total Orders
13,892 (COUNT would return 30,000)
Avg Order Value
€4,530
Total Profit / Margin
€25,282,189 · 40.18%
Digital Sales
€25,531,540 (40.57%)
Promotion Sales
€26,793,724 (42.58%)
% of All Sales
Leader 3.01% · Total 100.00%
Rep Status (IF)
20 above · 20 below · average €1,573,244
Rep Tier (SWITCH)
9 top · 21 middle · 10 bottom
Rep Rank (RANK)
Kenji Smith = 1 · Piotr Levi = 40
Leader Sales (INDEX)
€1,896,727 · last one 65.4%
Running Sales (WINDOW)
Last row = €62,929,759
Target Attainment %
99.86% (target €63,014,903)
Total Refunds
€2,364,425 (3.76%) in 1,649 rows
The leading product
Ardex Audio 1228 — €583,483
The profitable category
Office Supplies — 41.5% profit margin
Peak month
2025-07 — €2,114,749
All results were verified in pandas against Retail_StarSchema_PowerBI.xlsx
(30,000 sales rows). 11 relationships were checked programmatically — 0 orphan keys, 0 blank keys,
0 circular dependencies and 0 duplicate filter paths.
You can also navigate with the buttons at the top of the screen, keyboard arrows, and on phone with a swipe