AdventureWorks capstone assignment — Sales dashboard

Capstone assignment · AdventureWorks

Sales dashboard — assignment setup and structure

A one-page dashboard where every number comes from measures you write from scratch, with automatic color coding for country status. Working on AdventureWorks_Sales.xlsx — the model from the earlier lessons. 25 exercises, each on its own page with a full solution. Note: the download and the practice are On the computer only — Power BI Desktop doesn't exist on phone.

Dataset download — AdventureWorks_Sales.xlsx 7 tables · 121,253 sales rows · 14MB · For download on computer only

$109,809,274Total sales — the anchor number

121,253Sales rows

13Measures to write

4+4+1Cards · charts · shape

Before you start — check the active relationship. on the screen Model Make sure the active relationship (the solid line) between Sales toDate it DateKey → OrderDateKey. in the table Sales There are three date keys (Order · Due · Ship), and auto-detection picks one of them arbitrarily. A wrong relationship = every time chart tells the wrong story - you'll see this in exercise 2.5.

Matching brackets. Table[Column] = column · [measure] = square brackets without a table name. For every measure: right-click on the Sales in the panel Data ← New measure ← the formula ← Enter. The name must be exactly as it appears in the exercise.

Assignment structure

Part 1

13 DAX measures

Eight base measures — SUM, DISTINCTCOUNT, AVERAGE, CALCULATE and DIVIDE — and five window measures: RANK, WINDOW, OFFSET and INDEX.

Part 2

One-page dashboard

Background and title, a KPI tray, four cards, a bar chart, a line chart, a donut chart, a ranking table, and a Top N filter.

part 3–4

Traffic light and assembly

Rule-based conditional formatting on the countries chart, then assembly, alignment, and a self-check that the dashboard tells one story.

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 column Sales[Sales Amount].

Total Sales = SUM ( Sales[Sales Amount] )

creationRight-click on the table Sales ← New measure
checkDrag onto an empty card to make sure the number appears

Why SUM and not a calculated column: A single numeric column that’s summed. A "live" measure — it’s recalculated in every filter bubble, so the same measure can serve a card, a chart, and a table without writing it again.

result: $109,809,274 — the anchor number for the whole assignment.

1.2 · Total Orders — how many orders, really

Create a measure named Total Orders that counts distinct orders based on 'Sales Order'[Sales Order].

Total Orders = DISTINCTCOUNT ( 'Sales Order'[Sales Order] )

Note the column: Sales Order and not SalesOrderLineKey. An order with several items appears in several rows; DISTINCTCOUNT Filters out the duplicates, while COUNT It would return 121,253 — rows, not orders.

result: 31,455 orders out of 121,253 sales rows.

1.3 · Avg Line Value — the average

Create a measure named Avg Line Value That calculates the average of Sales[Sales Amount].

Avg Line Value = AVERAGE ( Sales[Sales Amount] )

Average of exactly what: This is an average per line item, not an average per order. The order average is obtained from[Total Sales] / [Total Orders] and stands at about $3,491 — almost 4x. Two completely different business questions, so it's important to know exactly what you divided.

result: $905.62 to the item row.

1.4 · Bikes Sales — first CALCULATE

Create a measure named Bikes Sales that returns sales of the Bikes category only. UseCALCULATE with [Total Sales] As an expression and a condition on Product[Category].

Bikes Sales = CALCULATE ( [Total Sales], Product[Category] = "Bikes" )

The heart of DAX: CALCULATE Runs an existing measure under a new filter. Note that we used[Total Sales] and we didn't write SUM again — the Lego principle: one change in the base measure cascades to every measure that relies on it.

result: $94,620,526.

1.5 · FY2020 Sales — second CALCULATE

Create a measure named FY2020 Sales that returns only the FY2020 fiscal year sales, with a condition on 'Date'[Fiscal Year].

FY2020 Sales = CALCULATE ( [Total Sales], 'Date'[Fiscal Year] = "FY2020" )

The same pattern, a different dimension: Once you understand CALCULATE, every \"sales of X\" is one row. Note that the year here is fiscal (July–June) and not calendar — FY2020 ends in June 2020.

result: $51,878,275.

1.6 · US Sales — U.S. sales

Create a measure named US Sales that returns only United States sales, with a condition on 'Sales Territory'[Country].

US Sales = CALCULATE ( [Total Sales], 'Sales Territory'[Country] = "United States" )

The number that drives the entire dashboard: The US alone generates more than half the business. We'll come back to it in window functions — it's the leading country, and the basis for "% of leader" comparisons.

result: $62,997,591 — 57.4% of all sales.

1.7 · Reseller Sales — by channel

Create a measure named Reseller Sales that returns sales through the Reseller channel, with a condition on 'Sales Order'[Channel].

Reseller Sales = CALCULATE ( [Total Sales], 'Sales Order'[Channel] = "Reseller" )

Channel insight: Two-thirds of the business goes through distributors rather than directly to the consumer. That's the starting point for any discussion of channel dependency.

result: $80,450,597 (73.3%). The remainder — Internet: $29,358,677 (26.7%).

1.8 · Bikes % of Total — ratio

Create a measure named Bikes % of Total — the share of the Bikes category out of total sales. UseDIVIDE with [Bikes Sales] and[Total Sales]. Format as a percent.

Bikes % of Total = DIVIDE ( [Bikes Sales], [Total Sales] )

the designMark the measure ← Measure tools ← Format = Percentage

Pure Lego: A measure built from two existing measures, without any SUM new. DIVIDE and not a slash — division by zero will silently return blank instead of an error.

result: 86.2% — one category holds almost the entire business.

1.9 · Country Rank — RANK

create a measure Country Rank that ranks countries by sales (highest = 1). UseRANK with ORDERBY on [Total Sales] in descending direction.

◆What is France's ranking, and why is RANK better than typing numbers manually?

Country Rank = RANK ( ALL ( SalesTerritory[Country] ), ORDERBY ( [Total Sales], DESC ) )

Why RANK and not manual numbers: The ranking is "live" — a year or category slicer will reorder it on its own, because ORDERBY Sorts by measure. Numbers typed in by hand would freeze the moment the user filters.

Two traps: The default of ORDERBY it ASC — without DESC The weakest country gets place 1. And inside a window function, always [measure] and not SUM raw, otherwise all rows will return position 2.

result: France = 5. The full ranking: US=1 · Canada=2 · Australia=3 · UK=4 · France=5 · Germany=6.

1.10 · Sales Running Total — WINDOW

create a measure Sales Running Total that sums from the leading country up to the current one, using WINDOW ( 1, ABS, 0, REL, ... ).

◆What's the cumulative value in the Australia row, and how do you check validity in the last row?

Sales Running Total =
CALCULATE (
    [Total Sales],
    WINDOW ( 1, ABS, 0, REL,
        ALL ( SalesTerritory[Country] ),
        ORDERBY ( [Total Sales], DESC, SalesTerritory[Country], ASC )
    )
)

The cumulative total by rank order
country	Total Sales	Running Total
United States	$62,997,591	$62,997,591
Canada	$16,355,770	$79,353,361
Australia	$10,655,336	$90,008,697
United Kingdom	$7,670,721	$97,679,418
France	$7,251,556	$104,930,974
Germany	$4,878,300	$109,809,274

The four numbers: 1, ABS = always start at the head of the list (a window that stays put) · 0, REL = stop at me (a window that moves with me). The tiebreaker inORDERBY Ensures a stable order when two countries are equal.

result: Australia = $90,008,697. The check: the last row must be exactly $109,809,274 — if not, the window is broken.

1.11 · Running % — Pareto in percentages

create a measure Running % — the running total from 1.10 as a percentage of total sales, with DIVIDE. Format as a percent.

◆What's the cumulative percent through the end of the third country, and what does that mean for the business?

Running % =
DIVIDE (
    [Sales Running Total],
    CALCULATE ( [Total Sales], ALL ( SalesTerritory[Country] ) )
)

Measure structure: The numerator is the running total, the denominator is the grand total freed from the country filter. Without ALL The denominator would shrink to the row itself and all rows would show 100%.

Result: up to Australia (place 3) — 82.0%. The list: 57.4% ← 72.3% ← 82.0% ← 89.0% ← 95.6% ← 100.0%. Pareto on a plate: half the countries generate 82% of the revenue.

1.12 · Gap To Above — OFFSET

create a measure Gap To Above — how far behind each country is from the one above it. UseOFFSET ( -1, ... ) inside CALCULATE, and are missing from-[Total Sales].

◆Which country is closest to overtaking the one above it, and by how much?

Gap To Above =
[Total Sales]
    - CALCULATE (
        [Total Sales],
        OFFSET ( -1,
            ALL ( SalesTerritory[Country] ),
            ORDERBY ( [Total Sales], DESC, SalesTerritory[Country], ASC )
        )
    )

The gap from the country above in the ranking
country	Gap To Above
United States	Empty — nothing above it
Canada	−$46,641,820
Australia	−$5,700,434
United Kingdom	−$2,984,615
France	−$419,165
Germany	−$2,373,255

What does OFFSET(-1) do: Jumps one row back in the ranked list and returns its value. The business insight: the tight chase happens at spots 4–5, not at the top — the gap between the US and Canada is a completely different order of magnitude.

result: France — only $419,165 from the UK.

1.13 · % of Leader — INDEX

create a measure % of Leader — what percent each country is of the leader. UseINDEX ( 1, ... ) To retrieve the leading sales, and DIVIDE.

◆What is Canada's % of Leader — and does "second place" tell the whole story?

% of Leader =
DIVIDE (
    [Total Sales],
    CALCULATE (
        [Total Sales],
        INDEX ( 1, ALL ( SalesTerritory[Country] ), ORDERBY ( [Total Sales], DESC ) )
    )
)

country	% of Leader
United States	100.0%
Canada	26.0%
Australia	16.9%
United Kingdom	12.2%
France	11.5%
Germany	7.7%

Ranking vs. proportion: "Second place" sounds close, but Canada is only a quarter of the US. RANK gives position, INDEX gives proportion — and only the combination tells the story.

result: Canada = 26.0%.

2.1 · Color hue and background

Choose one HEX shade (for example in coolors.co) — all the dashboard colors will be from its family. Click an empty area on the canvas ← Format your report page ← Canvas background ← a light version of the hue ← Transparency = 0%. Add a title: Insert → Text box ← "AdventureWorks Sales Report" in a large font and a dark color from the family.

backgroundFormat your report page ← Canvas background ← light tint ← Transparency = 0%
titleInsert ← Text box ← large font, a dark color from the family
Color disciplineOne hue plus its lightness shades. Every additional color has to justify itself

Check: if you need more than three colors to explain the dashboard — you have too many colors.

2.2 · The Shape — the KPI tray

Insert → Shapes → Rectangle ← stretch a horizontal strip below the title, where the four cards used to sit. Format shape → Rounded corners, fill with a light shade from the family. If the shape covers everything — Format → Arrange → Send to back.

the formInsert ← Shapes ← Rectangle At the width of the four cards
designRounded corners, fill in a light hue, no border
layersFormat ← Arrange ← Send to back

why tray: The shape groups the four cards into one visual object. The eye reads a "KPI area" instead of four floating squares.

Test: clicking a card selects the card and not the shape — a sign the shape is behind it.

2.3 · The four cards — measures only!

Build 4 cards above the tray, each showing a measure you wrote (not a raw field): [Total Sales] · [Total Orders] · [Avg Line Value] · [Bikes % of Total]. Build one card, format it fully (Callout value, font and color from the family, Visual border → Rounded corners), then duplicate it three times and swap the field. Align: Ctrl+Click On all four of them ← Align top → Distribute horizontally → Group.

The four values that should appear
card	the measure	value
Total sales	Total Sales	$109,809,274
Number of orders	Total Orders	31,455
Average row value	Avg Line Value	$905.62
% bikes	Bikes % of Total	86.2%

Design once and duplicate: Four cards styled separately will never come out identical. Duplicating guarantees the same font, the same corner radius and the same spacing.

Check: no raw field is dragged onto the card — all four values come from measures you wrote.

2.4 · Chart 1 — sales by country (columns)

Clustered column chart ← X-axis = 'Sales Territory'[Country] · Y-axis = [Total Sales] (the measure!). Sort descending: ··· → Sort axis → Total Sales → Sort descending. Add Y-axis → Display units = Millions andData labels = On, and hide unnecessary axis titles.

◆Which country leads in sales, and how many times bigger is it than the second?

country	Total Sales
United States	$62,997,591
Canada	$16,355,770
Australia	$10,655,336
United Kingdom	$7,670,721
France	$7,251,556
Germany	$4,878,300

The descending sort is critical: Without it Power BI sorts alphabetically, and Australia will appear first — an order that misleads the reader into thinking it is the leader.

result: United States Leads with $62,997,591 — 3.9x Canada.

2.5 · Chart 2 — monthly sales trend (line)

Line chart ← X-axis = 'Date'[Date] (from the date table!) · Y-axis = [Total Sales]. If you only see 3 points (years) — click the fork arrow (Expand all down one level) down to the month level, or leave only Month on the axis. Dashed horizontal gridlines, Y axis starting at 0.

◆In which month was the sales peak recorded?

This is the "wrong context" point from the opening warning: In Sales there are three date keys — Order, Due and Ship. If the active relationship isn't OrderDateKey, every point on the line will shift and tell the wrong story. If the chart looks odd — check on screen Model Who is the solid line.

Result: a record inMay 2020 — $5,265,797.

2.6 · Chart 3 — category breakdown (donut)

Donut chart ← Legend = Product[Category] · Values = [Total Sales]. Enable Format → Detail labels → Label contents = Percent of total, colors from the family, and highlight the largest slice.

◆Which category dominates, and by how much — and is that healthy for the business?

category	Total Sales	% of sales
Bikes	$94,620,526	86.2%
Components	$11,799,077	10.7%
Clothing	$2,117,613	1.9%
Accessories	$1,272,058	1.2%

The business insight: 86% in one category is product dependency — any shock in the bike market shakes the whole company. The donut turns the metric [Bikes % of Total] from 1.8 into a picture, and tells exactly the same concentration story as the countries table.

result: Bikes — 86.2% from the sales.

2.7 · Chart 4 — ranking table (Leaderboard)

Build a visual Table — scoreboard: Country · [Total Sales] · [Country Rank] · [% of Leader] · [Running %]. Sort by Country Rank ascending.

◆What does the Running % column tell us about market concentration — and at which row do we cross 82%?

The full scoreboard
country	Total Sales	Rank	% of Leader	Running %
United States	$62,997,591	1	100.0%	57.4%
Canada	$16,355,770	2	26.0%	72.3%
Australia	$10,655,336	3	16.9%	82.0%
United Kingdom	$7,670,721	4	12.2%	89.0%
France	$7,251,556	5	11.5%	95.6%
Germany	$4,878,300	6	7.7%	100.0%

Four measures, four questions: How much (Total Sales), where (Rank), relative to whom (% of Leader) and how much of the business is already covered (Running %). This is a table that replaces three charts.

result: Running % crosses 82% already by the third row — half the countries generate 82% of the revenue. Pareto in a single column, without a separate analysis.

2.8 · Top N — the three leading countries

Duplicate chart 2.4 and turn it into a Top 3: in the panel Filters, on Country ← Filter type = Top N ← Top 3 by [Total Sales] ← Apply. Add the [% of Leader] As a Tooltip.

◆Which three countries remain, and what is their cumulative percentage of total business sales?

Top N is a visual filter, not a measure: It doesn't change the data or create a new calculation — it only determines what the chart draws. The exact same measures keep working underneath it.

Hovering reveals the proportion: US 100% · Canada 26.0% · Australia 16.9% — this way the chart tells both "who" and "how much".

result: United States · Canada · Australia — together $90,008,697 = 82.0% of the business.

3.1 · Rules — country traffic-light

Color the country chart's columns (2.4) according to three rules on [Total Sales] — no new measure required. Select the chart ← Format visual → Columns → Colors ← fx ← Format style = Rules, What field = [Total Sales] ← three rules with New rule: red below 15M, orange 15M–25M, green from 25M and up.

◆Note the full assignment: which countries were colored green, which orange, and which red — and what does that say about the spread of sales?

The three rules — the value type must be Number
law	condition	color	who enters
1	>= 0 and < 15000000	red	Australia · United Kingdom · France · Germany
2	>= 15000000 and < 25000000	orange	Canada ($16.36M)
3	>= 25000000	green	United States ($62.99M)

the pitfall: The value type must be Number and not Percent — thresholds are written as full values (15000000). In Percent, Power BI will interpret them as 0–100 percentages and color all the columns the same color.

result: Just one green — only two countries cross the 15M threshold. Extreme concentration, exactly as the Running % showed.

4.1 · Assembly checklist — everything on one page

Assemble all the components into one business dashboard. Go through the checklist — every line checked before submitting.

11 components on one page
component	source	Recommended position
Title + background from the family	2.1	Page header, full width
KPI tray (shape)	2.2	A top strip beneath the header
4 cards	2.3	on the tray, aligned horizontally
Bar chart + traffic light	2.4 + 3.1	Middle-left
Line chart — monthly trend	2.5	Middle-right
Donut — categories	2.6	Bottom-left
Ranking table	2.7	Bottom-right

Check: title + tray + 4 cards + 4 charts = 11 components, all on one page.

4.2 · Layout and alignment (Layout)

Arrange the canvas by an imaginary grid of three rows: title + KPI at the top · two charts in the middle · donut and table at the bottom. Select any pair of adjacent visuals ← Format → Align ← align edges and margins. Keep an even gap between the elements, and don't let any element touch the edge of the canvas.

◆Are all 11 components on one page, clean and aligned?

networkThree rows: title+KPI · two charts · donut+table
alignmentFormat ← Align on every adjacent pair — uniform edges and spacing
breathingUniform spacing between elements, zero elements touching the edge
uniformitySame font and size for all headings, all colors from the 2.1 family

Layout is what turns a collection of visuals into a dashboard: The eye rests on an orderly grid and loses information in clutter. A three-pixel misalignment registers even if the viewer can't point to it.

Check: 11 elements in a three-row grid, uniform spacing, zero overflow from the canvas.

4.3 · Self-check — does the dashboard tell a story?

A good dashboard answers a business question at a glance. Go over the three review questions: centralization — can you immediately see that the US and Bikes dominate? consistency — do the cards, the charts, and the table show the same numbers? cleanliness — can a component be removed without losing information? If so, remove it.

◆Summarize in one sentence: what's the central business story your dashboard tells?

The main story: A very concentrated business. The US alone is 57.4% of sales (a single green traffic light), Bikes is 86.2% of the catalog (one donut slice), and three countries hold 82% (Top 3 / Running %). The dashboard needs to shout "concentration" at a single glance.

The consistency test: [Total Sales] In the card = the sum of all columns in the chart = 100% in the last row of Running % = the donut sum. If one of them doesn't match — there's a hidden filter on one of the visuals.

Cohesion is what distinguishes a dashboard from a collection of charts: card, color, slicer, and running total — they all tell the same story from different angles.

Summary — all the results in one place

A consolidated validation table for all 13 measures and every visual in the assignment. Open it only after you're done — it gives away all the answers.

All verified results in one place
Measure / question	Verified result
Total Sales	$109,809,274
Total Orders	31,455
Avg Line Value	$905.62
Bikes Sales / % of Total	$94,620,526 / 86.2%
FY2020 Sales	$51,878,275
US Sales	$62,997,591 (57.4%)
Reseller Sales	$80,450,597 (73.3%)
Country Rank: France	5 (US=1 ... Germany=6)
Sales Running Total: Australia	$90,008,697 (end = $109,809,274)
Running %: up to Australia	82.0%
Gap To Above: the smallest	France, −$419,165
% of Leader: Canada	26.0%
Chart 2.4 — leading country	United States, 3.9x Canada
Chart 2.5 — Peak month	May 2020 — $5,265,797
Chart 2.6 — category dominates	Bikes — 86.2%
Top 3 (2.8)	$90,008,697 = 82.0%
Traffic light (3.1)	US green · Canada orange · the remaining 4 red

All results were verified in pandas against AdventureWorks_Sales.xlsx (121,253 sales rows). The task combines SUM, DISTINCTCOUNT, AVERAGE, CALCULATE and DIVIDE, the window functions RANK, WINDOW, OFFSET and INDEX with ORDERBY, KPI columns, Rules, and the Lego principle.