Land shipping model — assignment prep and structure
Two dashboard pages where every number comes from a measure you write from scratch, with automatic color coding
for leader profitability. Working on Logistics_PowerBI_Star.xlsx — a pure star schema with one
fact table, seven dimensions and28,400 shipment rows. 29 exercises, each on a separate page and with a full\n solution. Note: the download and the practice are On the computer only — Power BI Desktop doesn't exist on phone.
8,466,765.26dollars of freight revenue — the anchor number
26,825,847.8Weighted kg
28,400Shipping rows
14Measures to write
The data is synthetic. The city and country names are real, but every customer, carrier, shipment, and number in the file was generated artificially with a fixed random seed. Nothing about the real shipping market should be inferred from them.
Before we start — building the model. The file has exactly seven relationships, all from the dimension to the fact table. Mark Dim_Date As a date table (Mark as date table on Full_Date), turn off Autodetect and built manually, make sure every relationship is One to many in the direction Single from the dimension to Fact_Shipments. Sheet Relationships The file lists the seven relationships one by one. There's no dimension-to-dimension relationship and no bridge table — every dimension touches the facts directly, and only them.
The big trap in this file. in the table of Dim_Carrier there is 12 leaders, but the fact table only shows 11. the row Danube Barge Union it A dimension row that no fact row points to. The practical, immediate meaning: a visual showing leaders will show 11 rows and not 12, because Power BI hides dimension values with no data by default; COUNTROWS ( Dim_Carrier ) will return 12 while DISTINCTCOUNT ( Fact_Shipments[Carrier_ID] ) will return 11; and in every window function that relies on ALLSELECTED ( Dim_Carrier[Carrier_Name] ) A blank row twelve will appear and will throw off the ranking, median, and cumulative percent if you do not filter it out. The two small traps: column Revenue_Per_KG_USD it's a ratio and must not be summed, and rows Cancelled andReturned carry unrealized revenue.
Matching brackets.Table[Column] = column · [measure] = square brackets without a table name. For each measure: right-click on Fact_Shipments in the panel Data ← New measure ← the formula ← Enter. The name must be exactly as it appears in the exercise, otherwise measures built on it won't find it.
Assignment structure
Part 1
14 DAX measures
Ten basic measures — two SUM, two DISTINCTCOUNT, DIVIDE, two CALCULATE, ALL, IF, and two SWITCH — plus three window functions: RANK, INDEX, and WINDOW. Plus one more exercise where instead of writing a measure, you explain why a naive answer is wrong.
Part 2
Two dashboard pages
KPI tray, smoothed line chart, slicer on a second page, detail table, Top N, Matrix, combo chart, donut vs. columns, small multiples, scatter and a polish page.
Part 3
Conditional formatting and insight
Two ways to color by profitability level — Field value vs. Rules — and one summary question: who's the leader that leads in revenue but isn't the most profitable.
The seven relationships in the model
from the dimension
To the fact table
type
Dim_Date[Date_ID]
Fact_Shipments[Ship_Date_ID]
1:Many · Single
Dim_Customer[Customer_ID]
Fact_Shipments[Customer_ID]
1:Many · Single
Dim_Carrier[Carrier_ID]
Fact_Shipments[Carrier_ID]
1:Many · Single
Dim_Route[Route_ID]
Fact_Shipments[Route_ID]
1:Many · Single
Dim_Service_Level[Service_Level_ID]
Fact_Shipments[Service_Level_ID]
1:Many · Single
Dim_Warehouse[Warehouse_ID]
Fact_Shipments[Warehouse_ID]
1:Many · Single
Dim_Cargo_Type[Cargo_Type_ID]
Fact_Shipments[Cargo_Type_ID]
1:Many · Single
All the numbers on the following pages were verified in pandas against the file
1.1 · Total Freight Revenue — the base measure
Create a measure named Total Freight Revenue that summarizes the Fact_Shipments[Freight_Revenue_USD]. Format with a thousands separator and two decimal places.
Total Freight Revenue =
SUM (
Fact_Shipments[Freight_Revenue_USD]
)
creationRight-click on Fact_Shipments ← New measure
designin the tab Measure tools choose Decimal number and2 digits
checkDrag onto an empty card to make sure the number appears
Note before you continue: This number includes all four statuses — including Cancelled and also Returned. This is booked revenue, not realized revenue. In exercise 1.6 we'll narrow it down, and you'll see exactly how much fell off.
result: 8,466,765.26 dollars — the anchor number of the whole assignment.
1.2 · Total Weight Shipped — a second column, not the same column
Create a measure named Total Weight Shipped that summarizes the Fact_Shipments[Weight_KG]. One decimal digit.
Total Weight Shipped =
SUM ( Fact_Shipments[Weight_KG] )
Why this specific column: The fact table also has Volume_M3, and it’s also fully additive. The difference is what the next measure will do with it — weight is the denominator conventionally used in the industry for shipping pricing, so it’s the one that will feed Revenue per KG In exercise 1.5.
result: 26,825,847.8 kg.
1.3 · Active Customers — the first distinct count
Create a measure named Active Customers that counts customers unique based on Fact_Shipments[Customer_ID].
Active Customers =
DISTINCTCOUNT (
Fact_Shipments[Customer_ID]
)
Why not COUNT:COUNT ( Customer_ID ) would return 28,400 — it counts rows that have a value, not unique values. Each customer sent on average 157.8 shipments over two years, which is why the gap is huge.
and why not count the dimension:COUNTROWS ( Dim_Customer ) it will return too 180, because in the case of customers, every dimension row really does appear in the facts. In exercise 1.12 you will see a dimension where this is exactly not true.
result: 180 Active customers.
1.4 · Routes Used — A Second Distinct Count, a Different Column
Create a measure named Routes Used that counts paths unique based on Fact_Shipments[Route_ID].
Routes Used =
DISTINCTCOUNT (
Fact_Shipments[Route_ID]
)
The difference from the previous two measures: The two sum measures worked on two different measure columns, and the two count measures work on two different foreign keys. This isn't repetition — each of the four touches a different column in the fact table, so each of them responds differently to filtering.
result: 60 Paths in use, out of 60 inDim_Route.
1.5 · Revenue per KG — the Lego principle
Create a measure named Revenue per KG — using only the two existing measures, without the division symbol / and without AVERAGE. Format to four digits.
Revenue per KG =
DIVIDE (
[Total Freight Revenue],
[Total Weight Shipped]
)
Why DIVIDE and not /: If the denominator comes out zero or blank — and a customer with no shipments in a given context is exactly such a case — the / operator returns an error that stains the entire visual, whereas DIVIDE Silently returns empty.
and why from two measures and not columns: When the measure is built from two existing measures, it accounts for the filter context in each cell individually and recalculates the numerator and the denominator. In exercise 1.15 we'll see exactly what happens when you try to take a shortcut.
result: 0.3156 Dollars per kg (8,466,765.26 partial 26,825,847.8).
1.6 · Delivered Revenue — the first CALCULATE
Create a measure named Delivered Revenue that returns the revenue of shipments with status Delivered only, using CALCULATE with one boolean filter.
What exactly does CALCULATE do here: It does not filter the measure — it changes the filter context within which the measure is calculated. The filter Fact_Shipments[Shipment_Status] = "Delivered" It's short forFILTER ( ALL ( ... ) , ... ), meaning it replaces Any status filter already present in the visual. If you place this measure in a table sliced by status, all rows will show the same number.
result: 7,444,128.27 dollars. The gap versus the base measure — 1,022,636.99 dollars, which are 12.08% recognized revenue not realized.
1.7 · On Time Delivered Revenue — Two Conditions with &&
Create a measure named On Time Delivered Revenue That returns the revenue of shipments that were both delivered and arrived on time: Shipment_Status = "Delivered"and alsoOn_Time_Status = "On_Time". You must use && inside a single condition, not two separate filters.
On Time Delivered Revenue =
VAR Ok =
FILTER (
Fact_Shipments,
Fact_Shipments[Shipment_Status]
= "Delivered"
&& Fact_Shipments[On_Time_Status]
= "On_Time"
)
RETURN
CALCULATE ( [Total Freight Revenue], Ok )
Why FILTER and not two boolean filters: Two separate filters in CALCULATE intersect and return exactly the same result here — but the exercise asks for one combined condition, and that's what && gives. The real difference shows up when you want or instead and also: the || It can't be expressed with two separate filters, and only FILTER will allow it.
The cost of FILTER: it scans the fact table row by row. For 28,400 rows it's not noticeable, on ten million it is. The rule: boolean filter when possible, FILTER when necessary.
result: 5,159,284.25 dollar — 69.31% of the revenue given.
1.8 · Pct of Grand Total — ALL on two dimensions
Create a measure named Pct of Grand Total that returns each cell's share of the total, with the denominator freed from any filter of second Dimensions all at once — Dim_Carrier andDim_Service_Level. Format as a percent.
Pct of Grand Total =
VAR Grand =
CALCULATE (
[Total Freight Revenue],
ALL ( Dim_Carrier ),
ALL ( Dim_Service_Level )
)
RETURN
DIVIDE (
[Total Freight Revenue],
Grand
)
Why ALL on the table and not on the column:ALL ( Dim_Carrier ) Neutralizes all dimension columns at once — also Carrier_Name and also Carrier_Mode and also Contract_Tier. If you write ALL ( Dim_Carrier[Carrier_Name] ) and in the Matrix you put the carrier type in the rows, the denominator will still filter by the type and the percentages won't sum to 100.
Matrix carrier × service level — sanity check
cell
Revenue in dollars
Percent of the total
Northwind Freight × Express
346,752.47
4.0955%
Northwind Freight — the whole row
1,655,574.35
19.5538%
Total of all cells
8,466,765.26
100.00%
The check that closes out the exercise: Turn on row and column totals in the Matrix. If the bottom-right cell shows exactly 100.00% - the denominator is correct. If it shows something else, ALL does not cover everything in the visual.
Result: the Northwind Freight × Express cell = 4.0955%; total number of cells 100.00%.
1.9 · Revenue vs Prev Month — IF against the previous period
Create a measure named Revenue vs Prev Month That returns the string Up If revenue in the current month is greater than or equal to revenue in the previous month, and otherwise Down. Use two VAR variables.
Revenue vs Prev Month =
VAR Cur = [Total Freight Revenue]
VAR Prev =
CALCULATE (
[Total Freight Revenue],
DATEADD (
Dim_Date[Full_Date],
-1,
MONTH
)
)
RETURN
IF ( Cur >= Prev, "Up", "Down" )
Why this only works after Mark as date table:DATEADD It's a time-intelligence function, and it requires a continuous, marked date table. Dim_Date contains 731 Consecutive days without a single gap — this isn't accidental, it's the requirement.
and why not subtract the previous month manually:Month_Number - 1 breaks in January. DATEADD knows how to cross a year.
Two checks worth running
month
income
Previous month
The measure result
2025-12
437,472.12
433,142.51
Up
2025-02
225,297.76
249,097.61
Down
Result: out of 23 Month-over-month comparisons in the file, 12 return Up. The strong month is 2025-10 with 507,396.90 dollar, and the weak one is 2025-02 with 225,297.76.
1.10 · Margin Tier — SWITCH ( TRUE () )
Create a measure named Margin Tier that classifies each carrier by its gross margin percentage into three tiers: Leader From 20% and up, Challenger From 12% and up, and everything else Laggard. Use SWITCH with TRUE.
Why SWITCH ( TRUE () ) and not nested IF: A nested IF at three levels is still readable, at six it's not anymore. SWITCH with TRUE keeps one column of conditions checked top to bottom, and the first one that's true wins — so Order is critical: if you put the 12% threshold first, every carrier above 20% will be classified as Challenger and none will reach Leader.
The actual classification — the 11 active leaders
leading
Revenue in dollars
spread %
level
Northwind Freight
1,655,570.42
20.30%
Leader
Rhine Express Group
1,467,050.34
22.88%
Leader
Baltic Haulage
1,037,060.51
13.75%
Challenger
Iberia Cargo Lines
874,699.14
16.35%
Challenger
Nordic Rail Freight
661,534.78
25.66%
Leader
Channel Sea Lines
646,970.08
27.93%
Leader
Skyway Air Cargo
526,805.38
12.25%
Challenger
Adriatic Transport
516,126.52
11.23%
Laggard
Alpine Rail Cargo
434,400.54
17.97%
Challenger
Atlantic Bulk Ships
378,313.23
29.55%
Leader
Meridian Air Freight
268,234.32
9.74%
Laggard
result: 5 Leader, 4 Challenger and2 Laggard. At the grand total level the measure returns Leader, because the total margin is 19.44%.
1.11 · Tier Color — SWITCH on a value
Create a measure named Tier Color That receives the result of Margin Tier and returns a hex color code. This is SWITCH on a value, not SWITCH with TRUE — note the difference.
The difference between the two SWITCHes: In the previous exercise the first argument was TRUE () and each branch was a complete boolean expression. Here the first argument is value — [Margin Tier] — and each branch is a value to compare. The second form is shorter and also faster, but it only works when comparing one value to a list of options.
Why three branches and not four: The last argument without a pair is the default. Without it, any unexpected value would return blank and the cell would be colored transparent.
Result: the measure returns #132449 For the five Leaders, #F5C518 to the four Challenger and#A9B4CC to both the Laggard. In Chapter III we'll connect it to conditional formatting.
1.12 · Carrier Rank by Shipments — RANK by quantity
Create a measure named Carrier Rank by Shipments that ranks the leaders by Number of shipments — not by income. Skip ranking (SKIP), from highest to lowest, with an explicit ORDERBY.
Why ALLSELECTED and not ALL:ALL It would also ignore slicers the user selected, and the ranking would always be against all 12 leaders. ALLSELECTED Neutralizes only the filter that the visual itself creates, and respects the slicers — so if the user filters to three leaders, the ranking runs 1 to 3.
and here we run into the trap.ALLSELECTED ( Dim_Carrier[Carrier_Name] ) returns 12 Names — including Danube Barge Union that has no fact row at all. COUNTROWS ( Fact_Shipments ) returns blank for it, and RANK's default (KEEP) pushes blanks to the end. So the last rank in the table will be 12 and not 11 — and if you activate Show items with no data You'll see it on the screen.
The full ranking
rating
leading
shipments
1
Northwind Freight
5,507
2
Rhine Express Group
4,900
3
Baltic Haulage
3,517
4
Iberia Cargo Lines
2,958
5
Nordic Rail Freight
2,270
6
Channel Sea Lines
2,118
7
Skyway Air Cargo
1,784
8
Adriatic Transport
1,712
9
Alpine Rail Cargo
1,481
10
Atlantic Bulk Ships
1,253
11
Meridian Air Freight
900
12
Danube Barge Union
empty
Result: Northwind Freight in first place with 5,507 shipments; Meridian Air Freight in position 11; and the orphan leader instead 12 with an empty value.
1.13 · Median Carrier Revenue — INDEX on the Median Value
Create a measure named Median Carrier Revenue that returns the leader's revenue the median — not the average and not the leader. Use INDEX with ascending ORDERBY, and first filter out leaders with no data.
Why FILTER on NOT ISBLANK: Without this row COUNTROWS ( Carriers ) would return 12 instead 11, MidPos would have come out 6 out of 12 — and that's no longer the median but the sixth item in a list whose first entry is empty. The file's trap shifts the result by an entire row.
Why ROUNDUP and not ROUND: in11 items, the median is the sixth item — 11 partial 2 it 5.5, and only rounding up gives 6. Regular rounding would give 6 Also, but in13 items (6.5) it would have given 6 instead 7.
What INDEX returns: not a number but row. That's why it sits as a filter argument inside CALCULATE, and not as a value returned directly.
result: 646,970.08 dollars — Channel Sea Lines, position6 out of 11 in ascending sort. An independent check in pandas on the same 11 gives values 646,970.08 — identical.
1.14 · Cumulative Pct of Revenue — a cumulative WINDOW in percent
Create a measure named Cumulative Pct of Revenue which returns the cumulative percentage of revenue, with the leaders sorted from largest to smallest. Use WINDOW from the very first row to the current row.
to read the four numbers:1, ABS = start at the very first row of the whole list, regardless of the current position. 0, REL = ends at the current row (0 rows from it). This is exactly the definition of a cumulative window. If you had written -2, REL instead 1, ABS, you would get a sliding window of three rows instead of accumulation.
Why two VARs and not one: The numerator is the sum of the window and the denominator is the total of that same list. Both use the same ALLSELECTED — and that's what guarantees the last percentage is exactly 100 and not 99.7.
The cumulative percentage, from largest to smallest
leading
Revenue in dollars
cumulative %
Northwind Freight
1,655,570.42
19.55%
Rhine Express Group
1,467,050.34
36.88%
Baltic Haulage
1,037,060.51
49.13%
Iberia Cargo Lines
874,699.14
59.46%
Nordic Rail Freight
661,534.78
67.27%
Channel Sea Lines
646,970.08
74.92%
Skyway Air Cargo
526,805.38
81.14%
Adriatic Transport
516,126.52
87.23%
Alpine Rail Cargo
434,400.54
92.36%
Atlantic Bulk Ships
378,313.23
96.83%
Meridian Air Freight
268,234.32
100.00%
Result: the top three leaders hold 49.13% of the revenue, and the last row reaches exactly100.00%.
1.15 · Explanation exercise — why the shortcut in the ratio column is wrong
In this exercise, you don't write a measure. A student looked at Fact_Shipments[Revenue_Per_KG_USD], saw that it already computes revenue per kg in every row, and instead of Exercise 1.5 wrote this:
Naive Revenue per KG =
AVERAGE (
Fact_Shipments[Revenue_Per_KG_USD]
)
Its result: 0.3815. The correct result from exercise 1.5: 0.3156. The gap is 20.9%. Explain in your own words why his measure is wrong, not just different. Answer before opening.
The answer: the average of ratios is not the ratio of averages.Revenue_Per_KG_USD It is a ratio column — numerator over denominator at the row level. When you compute AVERAGE on it, every shipment gets equal weight: a shipment of 30 kg affects the result exactly like a shipment of 20,000 kg.
and why it tilts upward specifically: In this file, revenue is built from a fixed component per shipment plus a component based on weight. Small shipments carry the same fixed component over very little weight, so their ratio is very high. They're numerous and light — so a simple average, which counts shipments, is inflated compared to a calculation weighted by kg.
When is it still allowed to sum a derived column: whenSUM equals applying the formula to the totals. Gross_Margin_USD Passes the test — the sum of the differences equals the difference of the sums, and therefore SUM ( Gross_Margin_USD ) Completely valid. Revenue_Per_KG_USD fails, and also its sum (10,834.60) is a meaningless number.
The rule to remember: Addition and subtraction pass through the schema, multiplication and division don't. Any column that's a ratio, percentage or average — you only touch it through a measure that recalculates from the numerator and denominator.
There's no measure to check here. The check is that you explained two things: that the weight in a simple average is the number of shipments, not the number of kg, and that the test is whether the summing happens through the operation.
2.1 · First page — background, title, and KPI tray
Build the skeleton of the first page: dark background, title, and a tray of four empty slots for cards. Still without the cards themselves — just the layout.
titleInsert ← Text box ← "Freight Performance" ← 28pt ← white
trayInsert ← Shapes ← Rectangle ← full #132449 ← corners 12 ← full width, height 140px
the page nameRight-click the tab ← Rename ← Overview
Why a tray and not four separate backgrounds: Four separate rectangles require manually aligning four heights. One rectangle behind four transparent cards stays aligned automatically, and also produces a continuous line the eye reads as a single row.
Layer order:Format ← Selection pane. The tray must be beneath the cards. If a card disappears — it's behind the rectangle, not deleted.
At the end of the exercise: a page named Overview, dark background, white title at the top, and an empty full-width dark rectangle. No data yet.
2.2 · Smoothed line chart — monthly revenue
add Line chart with Dim_Date[Year_Month] on the axis andTotal Freight Revenue in the values. Smooth the line, turn off the markers, and add an average line.
visualLine chart ← axis: Year_Month ← Values: Total Freight Revenue
smoothingFormat ← Lines ← Shape ← Smooth line ← On
thicknessLines ← Stroke width ← 3 ← color #F5C518
average lineAnalytics ← Average line ← Add ← dashed
axisY axis ← Display units ← Thousands
High and low in the file
month
Revenue in dollars
2025-10 — the peak
507,396.90
2025-02 — the low
225,297.76
2024-12
423,149.65
2025-12
437,472.12
When smoothing is misleading: A smoothed line draws a curve between the points, which is why it inventor values that don't exist between month and month. In a monthly series this is fine because the trend matters more than the point. In a series with a sharp jump — for example a policy change — smoothing will hide exactly what you wanted to show. There, turn it off.
Note the seasonality: September through December are consistently high in both years, and January–February are low. This is baked into the data, not noise.
At the end of the exercise: the line climbs to a peak of 507,396.90 in2025-10 and drops to225,297.76 in2025-02.
2.3 · Second page and a synced slicer
Create a second page named Detail, add a slicer on Dim_Service_Level[Service_Level_Name], and synced it so it works across both pages but is shown on only one.
page+ at the bottom ← Rename ← Detail
slicerSlicer ← field: Service_Level_Name
styleFormat ← Slicer settings ← Style ← Tile
sortingMore options ← Sort by ← Priority_Rank ← ascending
synchronizationView ← Sync slicers ← mark Sync On both pages, Visible only inDetail
Why sort by Priority_Rank and not by name: Alphabetical sorting will give Bulk, Economy, Express, Same_Day, Standard — order is meaningless. Priority_Rank Is found in the dimension exactly for this purpose, and gives a fast-to-slow order.
What sync does and does not do: it shares the the choice between pages, not the visual. If you change the formatting on one — the other won't change. And if you forget to check Sync on the second page, the slicer will only work where it's displayed.
Five service levels
level
Revenue in dollars
shipments
spread %
Standard
3,520,041.24
12,086
18.44%
Express
1,818,986.44
5,345
25.62%
Economy
1,595,228.22
5,926
14.47%
Bulk
870,028.97
3,296
11.54%
Same_Day
662,480.39
1,747
30.21%
At the end of the exercise: selecting Express on the Overview page also changes the Detail page, and the slicer itself is visible only on Detail.
2.4 · Top performers detail table
on the page Detail built Table with the carrier name, revenue, number of shipments, margin, and level. Five measures or columns, no more.
visualTable ← fields: Dim_Carrier[Carrier_Name], Total Freight Revenue, Carrier Rank by Shipments, Margin Tier
sortingClick on the revenue header ← descending
totalFormat ← Totals ← On
Column widthFormat ← Column headers ← Auto-size width ← Off
gridGrid ← Horizontal gridlines ← On ← #1E2A44
What you will see in the summary row: The revenue will sum to8,466,765.26 — correct. But Margin Tier In the summary row it will show Leader, because it's recalculated over all the data rather than summing the row-level evaluations. This isn't a bug — it's exactly what a measure is supposed to do.
and what you won't see: the Danube Barge Union. It exists in the dimension and has no data, so Power BI hides it. To see it: right-click the field in the pane Columns ← Show items with no data.
The expected table
leading
Revenue in dollars
rating
level
Northwind Freight
1,655,570.42
1
Leader
Rhine Express Group
1,467,050.34
2
Leader
Baltic Haulage
1,037,060.51
3
Challenger
Iberia Cargo Lines
874,699.14
4
Challenger
Nordic Rail Freight
661,534.78
5
Leader
Channel Sea Lines
646,970.08
6
Leader
Skyway Air Cargo
526,805.38
7
Challenger
Adriatic Transport
516,126.52
8
Laggard
Alpine Rail Cargo
434,400.54
9
Challenger
Atlantic Bulk Ships
378,313.23
10
Leader
Meridian Air Freight
268,234.32
11
Laggard
At the end of the exercise: 11 rows, the revenue totals8,466,765.26.
2.5 · Top 10 — the ten biggest customers
Add a horizontal bar chart of the top ten customers by revenue. UseTop N In the filters pane — not in manual sorting.
visualClustered bar chart ← axis: Dim_Customer[Customer_Name] ← Values: Total Freight Revenue
filterFilters ← Customer_Name ← Filter type ← Top N
definitionShow items ← Top ← 10 ← By value ← Total Freight Revenue ← Apply filter
labelsFormat ← Data labels ← On ← Display units ← Thousands
Why Top N and not sorting: Sorting arranges all 180 customers and shows the first ones that fit on screen — a number that changes with the size of the visual. Top N truly cuts to ten, and more importantly: it Responds to the slicer. If the user selects Express, you'll get the top ten in Express, not the overall top ten that happen to have Express.
Alternatively, in the measure: You can reach the same result with TOPN inside CALCULATE, and that's useful when you want the sum of the top ten as a single number:
Sanity check for the chapter: The bottom-right cell must show 100.00%. If it shows a different number - the ALL in the measure does not cover everything in the visual. This is exactly the test exercise 1.8 built the measure for.
Why Matrix and not Table: Table puts all the fields in a single column. Matrix creates a real intersection of two dimensions, and it's the only visual where a measure with ALL on two dimensions shows everything it knows.
Three checkpoints
cell
value
Northwind Freight × Express
4.0955%
Northwind Freight — row summary
19.5538%
The bottom-right cell
100.00%
At the end of the exercise: 11 rows on 5 columns, and the grand total 100.00%.
2.7 · Combo chart — columns vs. line on two axes
built Line and clustered column chart: the leaders on the axis, revenue in the columns, andRevenue per KG As a line on the secondary axis.
visualLine and clustered column chart
axisX axis ← Carrier_Name
columnsColumn y-axis ← Total Freight Revenue
lineLine y-axis ← Revenue per KG
secondary axisFormat ← Y axis ← Secondary ← Show On
The only rule that matters here: Two axes are permitted only when the two series are in different units. Revenue in dollars alongside a dollar-per-kg ratio — legitimate. Two dollar-denominated series on two different axes — visual manipulation, because you can make either series look higher just by choosing the scale.
What this chart reveals: Northwind Freight is the highest in the columns, but the line above it isn't the highest. That's exactly the question that will close out the assignment in exercise 3.3.
By the end of the exercise: bars descending from left to right, a line that doesn't descend with them, and two marked axes.
2.8 · Donut vs columns — same data, two readings
built Donut chart of revenue by Corridor_Name, and next to it a column chart of the exact same data. Compare.
donutDonut chart ← legend: Corridor_Name ← Values: Total Freight Revenue
columnsClustered column chart ← the same two fields
sortingin the columns: sorted descending by revenue
The three corridors
corridor
Revenue in dollars
percent
Cross_Border_Europe
6,909,598.68
81.61%
Intra_Europe_West
1,304,538.13
15.41%
Domestic
252,628.45
2.98%
When a donut chart is preferable: when there are three or four categories and the question is "how much of the whole". Here Cross_Border_Europe takes up more than three quarters, and that's read immediately in a donut chart.
and when it fails: Once there are seven categories or more, or when two slices are close in size — the eye can't compare angles. Try building a donut of 11 the leaders and see why. In columns, comparison is immediate.
At the end of the exercise: two visuals with the same three values, Cross_Border_Europe on 81.61%.
2.9 · Small multiples — a small grid instead of a cluttered chart
Take the bar chart of revenue by Dim_Date[Quarter_Name] and split it into small multiples by Dim_Service_Level[Service_Level_Name].
splitdrag Service_Level_Name to the field Small multiples
networkFormat ← Small multiples ← Layout ← 3 rows on 2 columns
Shared axisY axis ← Shared axis On
The decision that determines everything: a shared axis or not. With a shared axis you can compare the panels — and immediately see that Same_Day is five times smaller than Standard. Without a shared axis, each panel stretches to full height, each one looks large, and comparing between panels becomes a lie. The default is a shared axis, and most of the time that's also the right choice.
When not to use it: When there are more than eight or nine panels. Then every panel is too small to read, and Matrix is better.
By the end of the exercise: five panels, the same Y axis on all of them, and the Standard panel clearly standing out above the rest.
2.10 · Scatter chart — weight vs. revenue
built Scatter chart where every point is a leader: X-axis = Total Weight Shipped, Y axis = Total Freight Revenue, bubble size = number of shipments.
visualScatter chart
breakdownValues ← Dim_Carrier[Carrier_Name]
axesX: Total Weight Shipped · Y: Total Freight Revenue
sizeSize ← Count of Shipment_ID
trend lineAnalytics ← Trend line ← Add
The Scatter trap: If you forget to put a field inValues, Power BI draws One point — the grand total. This is the most common mistake in this visual, and it looks like a bug.
What the trend line tells: Revenue rises with weight, but not proportionally — carriers with an air-heavy mix earn more per kg. A carrier that sits above the line charges a high price per kg; one below it charges less.
At the end of the exercise: 11 bubbles, not one. If there's one bubble — a field is missing in Values.
2.11 · Navigation between pages and final polish
Connect the two pages with navigation buttons, align everything, and turn off what isn't needed.
alignmentMark visuals ← Format ← Align ← Align top ← Distribute horizontally
titlesFor every visual: Format ← Title ← short text in English ← 12pt
cleaningturn off Shadow in all the visuals, and give them all Border uniform #1E2A44
tab orderView ← Selection ← Tab order ← Arrange in reading order
The polish that pays off most: Alignment. Two visuals two pixels apart look sloppy even if the content is perfect, and the eye picks up on it before it reads a single number.
and what's most often forgotten:Tab order. Keyboard users move between visuals in the order they were created, which is almost never the reading order. Two minutes of work.
One last check before submitting:View ← Page view ← Actual size, then Fit to page. If something pops out — there's a visual spilling off the canvas.
By the end of the chapter: two linked pages, all visuals aligned, consistent titles and a logical tab order.
3.1 · Conditional formatting by a color measure
Color the cell background in the leaders table by Tier Color, using the method Field value. Then also add a text color so it stays readable.
openingIn the leaderboard table: Format ← Cell elements
Choosing a seriesApply settings to ← Total Freight Revenue
backgroundBackground color ← On ← fx
methodFormat style ← Field value ← What field should we base this on ← Tier Color
textRepeat the same track inFont color with the Tier Text Color measure
Tier Text Color =
SWITCH (
[Margin Tier],
"Leader", "#FFFFFF",
"Challenger", "#132449",
"#132449"
)
Why Field value and not Rules: Here the logic already lives in the measure [Margin Tier], and both series - the background and the text - depend on it. If tomorrow the threshold changes from 20% to 22%, you change one place and everything updates. With Rules, you'd have had to fix every visual separately.
The measure must return a valid hex string.#132449 works; 132449 Without the hashtag, no. If the cell stays white — that's the reason ninety percent of the time.
What will print on screen
level
background
text
leading
Leader
#132449
#FFFFFF
5
Challenger
#F5C518
#132449
4
Laggard
#A9B4CC
#132449
2
Result: the division on the screen is 5 / 4 / 2.
3.2 · The exact same split, using the Rules method
They arrived at exactly the same split — but without a color measure. They usedRules on the margin percentage. The goal: to prove to yourself that both approaches produce an identical result, and understand when each is preferable.
First create a helper measure for the gap vs. the target:
Margin Gap vs Target =
VAR TotMargin =
SUM (
Fact_Shipments[Gross_Margin_USD]
)
VAR Pct =
DIVIDE (
TotMargin,
[Total Freight Revenue]
)
RETURN Pct - 0.18
openingCell elements ← Background color ← fx
methodFormat style ← Rules
baseWhat field should we base this on ← Margin Gap vs Target
rule 1If value ≥ 0.02 ← #132449
rule 2If value ≥ -0.06 ← #F5C518
rule 3If value < -0.06 ← #A9B4CC
Why the thresholds look different: The measure returns a gap against an 18% target, so a 20% threshold becomes+0.02 and a threshold of 12% becomes-0.06. Same division, a different reference point.
The trap of Rules:Percent against Number in the menu. If you leave Percent, Power BI will interpret the thresholds as a percentile of the range rather than as a value — and the split will come out completely different. Choose Number.
When Rules is preferable anyway: When the logic belongs to a single visual only, when you need a continuous scale instead of three tiers, or when you're not allowed to create additional measures in the model.
Result: the exact same division — 5 / 4 / 2. If you get something else, check the Number against Percent.
3.3 · The summary question — the leader that leads but isn't the most profitable
The closing insight question. Northwind Freight leads in revenue with 1,655,570.42 dollar — 19.55% of all revenue in the file. Atlantic Bulk Ships generates 378,313.23 Dollars only. Which of the two is worth more to the company, and what will you do with the answer? Build the visual that answers the question, write two lines of conclusion, and only then open it.
The visual that answers: A scatter chart where X is revenue, Y is margin percentage, and the bubble size is the number of shipments. It immediately separates size from quality - something a table sorted by revenue would never do.
Two ends facing each other
leading
Revenue in dollars
Spread in dollars
spread %
Northwind Freight
1,655,570.42
336,144.47
20.30%
Atlantic Bulk Ships
378,313.23
111,779.12
29.55%
The answer: both, and not in the same way. Northwind Freight generates 336,144.47 a dollar of absolute margin — more than any other carrier in the file — so giving it up would be devastating even if its margin percentage is only 20.30%. Atlantic Bulk Ships generates the most efficient margin in the file, 29.55%, but on a small base. A margin in percent tells you how efficient each dollar is; a margin in dollars tells you how much money actually comes in. A decision that relies on only one of them will always be wrong.
The practical conclusion: The opportunity isn't to choose between them but to shift volume to Atlantic Bulk Ships — every dollar that moves to it generates roughly 9.25 An extra cent of margin from every dollar of revenue. The question that remains open is why it's small: a capacity limit, geographic coverage, or simply that no one looked.
and who really needs attention: Adriatic Transport and Meridian Air Freight — the two Laggards. They're not the smallest, and that's exactly why they cost money: significant volume at a thin margin.
Result: Northwind Freight leads in absolute margin, Atlantic Bulk Ships leads in percentage, and the two Laggards are the target for repricing.
Summary — all the results in one place
A centralized validation table for all 14 measures and every sanity check in the assignment. Open it only after you're done — it gives all the answers.
All verified results in one place
measure
function
Verified result
Total Freight Revenue
SUM
8,466,765.26 dollar
Total Weight Shipped
SUM
26,825,847.8 kg
Active Customers
DISTINCTCOUNT
180
Routes Used
DISTINCTCOUNT
60
Revenue per KG
DIVIDE
0.3156
Delivered Revenue
CALCULATE
7,444,128.27 dollar
On Time Delivered Revenue
CALCULATE + &&
5,159,284.25 dollar
Pct of Grand Total
ALL
4.0955% to Northwind Freight × Express · total 100.00%
Revenue vs Prev Month
IF
Up in2025-12 · Down in2025-02
Margin Tier
SWITCH ( TRUE () )
5 Leader · 4 Challenger · 2 Laggard
Tier Color
SWITCH
#132449 · #F5C518 · #A9B4CC
Carrier Rank by Shipments
RANK
Northwind Freight first · Danube Barge Union in 12 With an empty value
Median Carrier Revenue
INDEX
646,970.08 dollars — Channel Sea Lines
Cumulative Pct of Revenue
WINDOW
49.13% for the top three · 100.00% in the last row
Additional sanity checks
check
value
Rows in the fact table
28,400
Leaders in the dimension vs. in the facts
12 against 11
The naive measure on the ratio column
0.3815 against 0.3156 the correct one
Unrealized revenue
1,022,636.99 dollar
Total gross margin
1,646,331.51 dollar · 19.44%
Rows with a negative gap (intentional)
1,911 rows · -34,038.47 dollar
Days in Dim_Date
731 consecutive
Peak month
2025-10 · 507,396.90
Low month
2025-02 · 225,297.76
If all 14 of your measures return exactly these values — the model was built correctly and the assignment is complete.
You can also navigate with the buttons at the top of the screen, keyboard arrows, and on phone with a swipe