Space Launches Capstone — Star Schema in Power BI

Capstone assignment · Space launches

Space launches model — assignment setup and structure

Two dashboard pages where every number comes from a measure you write from scratch, with automatic color indication for agency status. Working on Space_PowerBI_Model.xlsx — a star schema with 32 tables, 100,000 rows and 44 relationships. 27 exercises, each on a separate page and with a full solution. Note: the download and practice are On the computer only — Power BI Desktop doesn't exist on phone.

Dataset download — Space_PowerBI_Model.xlsx 32 tables · 100,000 rows · 44 relationships · 6MB · For download on computer only

35,093,955kg of cargo — the anchor number

50,794Cargo-launch rows

1,500launches

14Measures to write

The data is synthetic. The country and region names are real, but every agency, payload, launch and number in the file was artificially generated. Nothing should be inferred from them about the real space industry.

Before we start — building the model. The file has 44 relationships; the assignment relies on ten of them. The table further down the page lists them one by one. Mark the Dim_Date As a date table (Mark as date table on Full_Date),\n turn off Autodetect and build manually, and make sure all relationships are in the direction Single From the dimension to the fact — the file has 11 fact tables that share dimensions, andBoth Produces a loop.

The big trap in this file. in the table of Fact_Launches There's a text column Prime_Agency_Name that looks exactly like the column in Dim_Agency, but It has no relationship at all in the model. A visual built on it won't respond to the agencies slicer and can't be sorted. Moreover: a measure that sums a column from-Fact_Launches won’t respond to the agency filter at all, because the filter flows from the dimension to the detail tables and not upward to the header table. Always drag the agency name fromDim_Agency.

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

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 slicer, an agencies table, Top 10, 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 Mass Delivered — the base measure

Create a measure named Total Mass Delivered that summarizes the Fact_PayloadLaunchStats[Mass_Delivered_KG]. Format with a thousands separator.

Total Mass Delivered = SUM ( Fact_PayloadLaunchStats[Mass_Delivered_KG] )

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

A consistency check worth running immediately: Three different tables in the file return exactly the same number — SUM(Fact_PayloadLaunchStats[Mass_Delivered_KG]), SUM(Fact_AgencyLaunchStats[Mass_Delivered_KG]) and SUM(Fact_Launches[Total_Mass_KG]). This isn't a coincidence: the data was built bottom-up, from the payload to the launch. If one of the three comes out different for you — something broke in the loading, not in the model.

result: 35,093,955 kg — the anchor number of the whole assignment.

1.2 · Launches Flown — how many launches actually

Create a measure named Launches Flown That counts shipments unique based on Fact_PayloadLaunchStats[Launch_ID].

Launches Flown = DISTINCTCOUNT ( Fact_PayloadLaunchStats[Launch_ID] )

Why not COUNT: COUNT(Launch_ID) would return 50,794 — it counts rows that have a value, not unique values. Each trip carries 33.9 charges on average (rideshare structure), so 50,794 ÷ 1,500 ≈ 33.9.

result: 1,500 launches.

1.3 · Mass per Launch — the Lego principle

Create a measure named Mass per Launch — strictly from the two existing measures, without the division sign / and without AVERAGE. Format to two decimal places.

Mass per Launch = DIVIDE ( [Total Mass Delivered], [Launches Flown] )

The average of what: AVERAGE(Mass_Delivered_KG) would have returned the average per cargo, not per launch. When the measure is built from two existing measures, the denominator is explicitly defined and you know exactly what you divided. DIVIDE Preferable to / because it returns blank instead of an error when the denominator is zero.

The measure automatically rolls up to any context: Drag it onto the agencies table and it will return the rate for each agency separately — see the insight in exercise 2.5.

result: 23395.97 Kg for launch.

1.4 · Total Telemetry Packets — the second measure

Create a measure named Total Telemetry Packets that summarizes the Fact_PayloadLaunchStats[Telemetry_Packets]. The measure serves the combo chart in exercise 2.11.

Total Telemetry Packets = SUM ( Fact_PayloadLaunchStats[Telemetry_Packets] )

result: 23,875,538 Telemetry packets.

1.5 · Deep Space Mass — first CALCULATE

Create a measure named Deep Space Mass that returns the mass for missions beyond Earth only. Use CALCULATE with [Total Mass Delivered] As an expression and a condition on Dim_OrbitClass[Orbit_Regime].

Deep Space Mass =
CALCULATE ( [Total Mass Delivered], Dim_OrbitClass[Orbit_Regime] = "Deep Space" )

What happens here: CALCULATE adds the condition to the filter bubble and only then runs the [Total Mass Delivered]. The filter sits on a text column in the dimension table, flows through the relationship to Fact_Launches and from there to the 50,794 fact rows — without any JOIN.

result: 2,073,879 kg — 5.91% of the total mass.
The full breakdown: Low Earth Orbit 15,410,517 · Sun-Synchronous Orbit 7,574,151 · Medium Earth Orbit 6,331,715 · Geostationary Transfer 3,703,693 · Beyond Earth Orbit 2,073,879

1.6 · Prime Mass — CALCULATE on another fact table

Create a measure named Prime Mass that returns the mass supplied by the primary agency. note: inFact_PayloadLaunchStats There is no such division — it's in Fact_AgencyLaunchStats[Manifest_Role].

Prime Mass =
CALCULATE (
    SUM ( Fact_AgencyLaunchStats[Mass_Delivered_KG] ),
    Fact_AgencyLaunchStats[Manifest_Role] = "Prime"
)

Why not on the cargo table: The Prime/Partner split simply doesn't exist there. It's inFact_AgencyLaunchStats, with one row per agency per launch. Both measures live under the same slicers because both tables are connected to the same dimensions - and that's exactly what a good star schema gives you.

Sanity check: 17,573,747 + 17,520,208 = 35,093,955. When you split into exhaustive parts, the sum of the parts must equal the whole.

result: 17,573,747 kg — 50.08% (Partner: 17,520,208).

1.7 · % of Program Mass — ALL releases the filter

Create a measure named % of Program Mass: Divide the [Total Mass Delivered] in total — a CALCULATE that removes the filter fromDim_Agency using ALL. Format as a percentage, two digits.

% of Program Mass =
DIVIDE (
    [Total Mass Delivered],
    CALCULATE ( [Total Mass Delivered], ALL ( Dim_Agency ) )
)

What ALL does: The numerator lives in the row's bubble (one agency), and the denominator is released from filtering and therefore always holds the entire program. In the Total row the numerator already equals the denominator — and that's the check that the measure was written correctly.

Result: the leading agency holds 3.42% · The Total row shows 100.00%.

1.8 · Agency Status — IF vs. the average

Create a measure named Agency Status that returns text: "Above average" if the agency exceeded 548,000 kg, and otherwise "Below average". Where does the number come from: Total divided by the 64 agencies, rounded.

Agency Status =
IF ( [Total Mass Delivered] >= 548000, "Above average", "Below average" )

Where does 548,000 come from: 35,093,955 ÷ 64 = 548343.05, rounded. The measure returns text, so it fits a table, not a numeric card.

result: 30 Agencies "above average" · 34 "Below average".

1.9 · Agency Tier — SWITCH into three tiers

Create a measure named Agency Tier That classifies each agency by [Total Mass Delivered]: "top" from 703,000 and above, "middle" from 456,000 and above, and otherwise "bottom". Use the pattern SWITCH ( TRUE (), … ).

Agency Tier =
SWITCH (
    TRUE (),
    [Total Mass Delivered] >= 703000, "Top",
    [Total Mass Delivered] >= 456000, "Middle",
    "Bottom"
)

Order determines: SWITCH stops at the first condition that's met. If the lower condition is written first, the entire top tier will be classified as "middle" and the top tier will stay empty. The last argument, with no condition before it, is the default.

result: 12 "top" · 30 "middle" · 22 "bottom".

1.10 · Tier Color — a SWITCH that returns a color

Create a measure named Tier Color that returns a HEX code based on the value of [Agency Tier]: top #132449, middle #F5C518, default #A9B4CC. Here's the regular SWITCH pattern, without TRUE().

Tier Color =
SWITCH (
    [Agency Tier],
    "Top", "#132449",
    "Middle", "#F5C518",
    "#A9B4CC"
)

The two forms of SWITCH side by side: In the previous exercise we used TRUE() because the conditions are range comparisons; here the expression is a text measure and the values are exact matches.

The common trap: SWITCH compares strings with an exact match. A single trailing space at the end of a string will fail silently, without an error message, and all the rows will get the default. Copy the strings from the previous measure, don't retype them.

The measure isn't shown in any visual — it's used only for the conditional formatting in exercise 3.2.

1.11 · Agency Rank — RANK

Create a measure named Agency Rank that ranks the agencies by [Total Mass Delivered] Using RANK on ALL ( Dim_Agency[Agency_Name] ), with ORDERBY in descending order. Requires Power BI from the 2023 version onward.

Agency Rank =
RANK ( ALL ( Dim_Agency[Agency_Name] ), ORDERBY ( [Total Mass Delivered], DESC ) )

What ALL does: removes the row filter and returns all 64 agencies to the list of contenders — without it, in a single agency's row there's a single contender and no one to rank.

What's different from RANKX: The default of ORDERBY is ASC, and therefore without DESC The weakest agency will get rank 1 — the opposite of RANKX. In the Total row the function returns blank, because there's no "current row" to locate in the list.

result: Perigee Space Group = 1 · Vega Astronautics = 64.

1.12 · Leader Mass and % of Leader — INDEX

Create a measure named Leader Mass that returns in every row the mass of the first agency in the list — wrap the [Total Mass Delivered] In CALCULATE with INDEX ( 1, … ). bonus: measure % of Leader — DIVIDE of the base measure by [Leader Mass], as a percent with one digit.

Leader Mass =
CALCULATE (
    [Total Mass Delivered],
    INDEX ( 1, ALL ( Dim_Agency[Agency_Name] ), ORDERBY ( [Total Mass Delivered], DESC ) )
)

% of Leader = DIVIDE ( [Total Mass Delivered], [Leader Mass] )

A fixed money column — the leader's value repeats on every row
agency	Total Mass	Leader Mass	% of Leader
Perigee Space Group	1,199,926	1,199,926	100.0%
Aurora Space Works	1,174,810	1,199,926	97.9%
Vega Space Industries	1,075,868	1,199,926	89.7%
Orbital Launch Systems	980,778	1,199,926	81.7%
Vega Astronautics	116,252	1,199,926	9.7%

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. Once every row has the leader's value, every "distance from the leader" comparison is a single DIVIDE.

result: 1,199,926 kg in each row · the last one stands at 9.7% of it.

1.13 · Running Mass — WINDOW

Create a measure named Running Mass — a running total from the start of the list to the current row, using WINDOW ( 1, ABS, 0, REL, … ) With the same ALL and the same ORDERBY.

Running Mass =
CALCULATE (
    [Total Mass Delivered],
    WINDOW ( 1, ABS, 0, REL,
        ALL ( Dim_Agency[Agency_Name] ),
        ORDERBY ( [Total Mass Delivered], DESC )
    )
)

The window grows row by row up to the total
agency	Total Mass	Running Mass
Perigee Space Group	1,199,926	1,199,926
Aurora Space Works	1,174,810	2,374,736
Vega Space Industries	1,075,868	3,450,604
Lyra Launch Systems	205,083	34,800,181
Kepler Orbital Services	177,522	34,977,703
Vega Astronautics	116,252	35,093,955

The logic of the four numbers: 1, ABS = starting at position 1 absolute, the head of the list · 0, REL = stop 0 steps from me, meaning at me. So the window accumulates. If you replace it with1, ABS ← 3, ABS You'll get a fixed column with the sum of the three largest — one word turns a moving window into a fixed one.

A built-in sanity check: the last row must be exactly 35,093,955 — the total mass in the file. If not, something in the window is broken.

1.14 · Total Slot Revenue — a third fact table

Create a measure named Total Slot Revenue that summarizes the Fact_SlotSales[Revenue_USD]. Format with Display units = Millions.

Total Slot Revenue = SUM ( Fact_SlotSales[Revenue_USD] )

Revenue breakdown by cargo class
Cargo class	income
Primary Payload	64,428,604,200
Rideshare Large	25,846,295,508
Rideshare Small	12,126,905,280
Return Cargo	11,385,112,950
Hosted Payload	4,547,006,280
CubeSat Slot	4,092,674,040

Why this matters: This measure proves that the model connects three fact tables under the same slicers. Pick a year in the slicer — all three measures (mass, telemetry portions, and revenue) will update together.

result: 122,426,598,258.00 dollars — about 122.4 billion.

2.1 · Background, title, and KPI tray

Background, title, and KPI tray — page 1 of the dashboard.

shadesOnly three: navy #132449, gold #F5C518 and neutral gray
backgroundClick on an empty canvas ← Format your report page ← Canvas background ← a very light version of the navy ← Transparency = 0%
titleInsert ← Text box ← "Control Room" ← large font, dark color from the family
trayInsert ← Shapes ← Rectangle ← horizontal strip ← Rounded corners ← Send to back

The shape must sit below to the cards. If it covers them — Format ← Arrange ← Send to back.

2.2 · Images — search and add

Images — search and add — dashboard page 1.

searchRocket or satellite on a transparent background — add transparent PNG for search, and filter byTools ← Color ← Transparent
sizeA file at least 300px wide; too small will look blurry when you stretch it
additionInsert ← Image ← the header corner on page 1, about 60 pixels tall
ratioFormat image ← Scaling = Fit. Resizing is always from a corner, not from the side

Don't use the logo of a real space agency. These are trademarks, and the data in the file is fictional anyway — an official logo on fictional data is exactly what's not allowed. Use a generic image from Unsplash or Pexels.

2.3 · The four cards — measures only

Build 4 cards above the tray, each showing a measure you wrote. Style one card fully, then duplicate it ×3 and swap the field.

Total Mass Delivered · Launches Flown · Mass per Launch · Total Slot Revenue

The four cards and the verified values
card	the measure	The value to display
Total mass	Total Mass Delivered	35,093,955
launches	Launches Flown	1,500
Kg for launch	Mass per Launch	23395.97
revenues	Total Slot Revenue	122.4B

Two display traps: (1) Launches Flown must Display units = None, otherwise "1.5K" will show instead of "1,500". (2) On the revenue card, set decimal places = 1 explicitly — by default Power BI chooses on its own and the number of digits won't be consistent with the other cards.

align: Ctrl+Click On all four of them ← Group ← Distribute horizontally ← Align top.

2.4 · Bar chart — mass by agency

built Clustered column chart with Dim_Agency[Agency_Name] on the X axis and[Total Mass Delivered] On the Y axis. Descending sort, Data labels = On, Y-axis from 0. 64 agencies is a lot — add Top N = 20 In the Filters pane.

X-axis = Dim_Agency[Agency_Name]
Y-axis = [Total Mass Delivered]     ← the measure, not the column
Sort  = Total Mass Delivered, descending

The top six and the bottom three
#	agency	mass	% of the total
1	Perigee Space Group	1,199,926	3.42%
2	Aurora Space Works	1,174,810	3.35%
3	Vega Space Industries	1,075,868	3.07%
4	Orbital Launch Systems	980,778	2.79%
5	Sirius Astronautics	828,125	2.36%
6	Vega Launch Alliance	798,482	2.28%
62	Lyra Launch Systems	205,083	0.58%
63	Kepler Orbital Services	177,522	0.51%
64	Vega Astronautics	116,252	0.33%

The important insight — and a classic interpretation trap: Perigee Space Group leads with 1,199,926 kg and Vega Astronautics closes with 116,252 — a gap of 10.3x. But drag the [Mass per Launch] to the same table and you'll get 15,383 vs. 12,916 — a gap of only 1.2x. Most of the gap isn't performance but Scope of activity: 78 launches versus 9. That's exactly the difference between a cumulative metric and a ratio metric, and that's the reason a dashboard with sums alone can be completely misleading.

Note where you dragged the agency name from: fromDim_Agency and not fromFact_Launches[Prime_Agency_Name]. The second one looks identical but isn't connected to the model.

the leader: Perigee Space Group — 1,199,926 kg (3.42% of the total).

2.5 · Line chart — trend over time

built Line chart with Dim_Date[Full_Date] on the X axis and[Total Mass Delivered] on the Y axis. If you only see 12 points — click the fork-shaped arrow (Expand all down one level) down to the Month level.

X-axis = Dim_Date[Full_Date]
Y-axis = [Total Mass Delivered]

Mass by year
year	mass
FY2014	1,880,564
FY2015	2,136,061
FY2016	2,276,817
FY2017	2,504,439
FY2018	2,616,189
FY2019	2,771,363
FY2020	2,995,164
FY2021	3,272,108
FY2022	3,615,444
FY2023	3,768,619
FY2024	3,560,131
FY2025	3,697,056

What we see: The launch program grew by 96.6% over 12 years, from 80 to 165 launches per year. The peak was in FY2023 with 3,768,619 kg.

Bonus question: Is the growth due to more launches or to heavier launches? Drag the [Mass per Launch] for the same chart — the answer is immediate.

Why you must Mark as date table: Not every day has a launch. Without marking the date table, Power BI doesn't know that the missing days are days without an event.

Peak in FY2023 · trough in FY2014 with 1,880,564 kg.

2.6 · Page 2 and the year slicer

Open a second page named "Agency and Cargo Spotlight" with the same background and the same color family, and add a year slicer.

page＋ at the bottom ← name: Agencies & Shipments Spotlight ← same canvas background — consistency
slicerSlicer ← field Dim_Period[Year_Label] ← Format ← Slicer settings ← Style = Tile
locationStretch across the width below the title · selected = the dark shade, unselected = light

Check that the slicer affects all the visuals on the page. A visual that doesn't respond is probably built on a column fromFact_Launches and not from the dimension.

2.7 · The agencies table — this is where IF and SWITCH live

built Table with Dim_Agency[Agency_Name] and the seven measures: Rank, Total Mass, % of Program Mass, % of Leader, Running Mass, Agency Status and Agency Tier. Sorted descending, Totals = Off.

Dim_Agency[Agency_Name] · [Agency Rank] · [Total Mass Delivered]
[% of Program Mass] · [% of Leader] · [Running Mass]
[Agency Status] · [Agency Tier]

The top four and the last two
rating	agency	mass	% of the total	Status	Tier
1	Perigee Space Group	1,199,926	3.42%	Above average	top
2	Aurora Space Works	1,174,810	3.35%	Above average	top
3	Vega Space Industries	1,075,868	3.07%	Above average	top
4	Orbital Launch Systems	980,778	2.79%	Above average	top
63	Kepler Orbital Services	177,522	0.51%	Below average	bottom
64	Vega Astronautics	116,252	0.33%	Below average	bottom

The difference between the two measures: Agency Status Compares to the average calculated from the data (548343.05), and-Agency Tier Compares to fixed thresholds we set. An agency can be "below average" and still in the "middle" tier — two measures, two different business questions.

Why Totals = Off: Window functions return blank in the summary row, because there's no\n "current row" to locate in the list.

30 agencies "above average" · tier split: 12 / 30 / 22.

2.8 · Top 10 Cargo — Bar with a Top N Filter

built Clustered bar chart with Dim_Payload[Payload_Designation] on the Y axis and[Total Mass Delivered] on the X axis. In the Filters pane: Filter type = Top N ← Show items: Top 10 ← By value = [Total Mass Delivered].

Y-axis = Dim_Payload[Payload_Designation]
X-axis = [Total Mass Delivered]
Filter = Top N, 10, by [Total Mass Delivered]

The ten heaviest cargoes
#	charger	type	kg
1	PLD-1105	Weather Satellite	164,460
2	PLD-1107	Lunar Lander	154,839
3	PLD-1089	Navigation Satellite	147,249
4	PLD-1106	Space Telescope	139,559
5	PLD-1373	Microsatellite	137,067
6	PLD-1356	Communications Satellite	128,738
7	PLD-0924	Earth Observation Satellite	122,857
8	PLD-0018	Propellant Tanker	121,797
9	PLD-0232	Orbital Transfer Vehicle	119,904
10	PLD-1906	Cargo Module	119,522

Note the kernel: Every row here is one payload across all 12 years, not a single launch. A payload that accumulated a lot did so mainly because it was launched many times — add a column with [Launches Flown] and you'll see it immediately.

the leader: PLD-1105 (Weather Satellite) — 164,460 kg.

2.9 · Matrix — country × track department

built Matrix with Dim_Country[Country] in rows, Dim_OrbitClass[Orbit_Class] in columns and[Total Mass Delivered] in values. Style presets = Minimal · Cell elements ← Data bars = On.

Rows    = Dim_Country[Country]
Columns = Dim_OrbitClass[Orbit_Class]
Values  = [Total Mass Delivered]

The top nine countries out of 16
country	Low Earth Orbit	Sun-Synchronous Orbit	Medium Earth Orbit	Geostationary Transfer	Beyond Earth Orbit	total
United States	3,189,171	1,460,931	1,231,718	707,824	422,619	7,012,263
China	1,322,212	744,826	544,365	235,945	209,354	3,056,702
Kazakhstan	966,270	401,148	420,032	302,156	136,607	2,226,213
Sweden	936,760	412,739	383,796	253,154	95,444	2,081,893
Brazil	763,487	457,413	389,143	220,351	145,690	1,976,084
United Kingdom	729,326	310,082	304,439	333,732	76,237	1,753,816
Spain	738,152	350,531	308,180	145,898	140,136	1,682,897
South Korea	766,649	248,395	312,730	177,428	131,191	1,636,393
Canada	543,443	333,730	263,176	233,717	39,222	1,413,288

Note the filter path: The country is connected toDim_Agency, which is connected to the cargo table. A chain of two relationships — and that's exactly why the model must be clean of duplicate paths.

Total of all cells in the table: 35,093,955 kg — identical to the base measure.

2.10 · Combo chart — mass vs telemetry by cargo class

built Line and clustered column chart with Dim_PayloadType[Payload_Class] on the X axis, [Total Mass Delivered] in columns and[Total Telemetry Packets] in the line. The columns in navy, the line in gold.

X-axis        = Dim_PayloadType[Payload_Class]
Column y-axis = [Total Mass Delivered]
Line y-axis   = [Total Telemetry Packets]

Two series, two units of measure
Cargo class	mass	% of the mass	telemetry	% of the telemetry
Satellite	10,640,236	30.3%	7,185,145	30.1%
Science	8,590,488	24.5%	5,564,277	23.3%
SmallSat	6,945,061	19.8%	4,780,406	20.0%
Logistics	6,487,932	18.5%	4,775,942	20.0%
Exploration	2,430,238	6.9%	1,569,768	6.6%

Why a combo chart: The two measures are measured in completely different units. In a regular bar chart the small series would get squashed to the bottom; two axes give each series its own scale. This is exactly the case where the rule "don't use two axes" doesn't apply.

The leading department: Satellite — 30.3% of the mass.

2.11 · Navigation and polish

Add navigation buttons between the two pages and finish the polish.

navigationInsert ← Buttons ← Navigator ← Page navigator ← place at the bottom center ← style in the family's colors
consistencythem Rounded corners, same intervals, Align for each row
checkZoom 100%: no text is cut off and no visual touches the edge of the canvas

Final check: hide the title — it should still be immediately clear this is a report about space launches.

3.1 · Rules on the Bar Chart

Color the column chart (exercise 2.4) with three rules based on [Total Mass Delivered]. No new measure is required.

Rule 1:  >= 0        and <  456000     →  #A9B4CC
Rule 2:  >= 456000   and <  703000     →  #F5C518
Rule 3:  >= 703000                     →  #132449

openingSelect the bar chart ← Format visual ← Columns ← Colors ← click fx
definitionFormat style = Rules · What field = [Total Mass Delivered]
rulesThree rules with New rule
criticalValue type = Number, not Percent ← OK

The result: 12 agencies in dark navy, 30 in gold, 22 in light navy.

3.2 · Field value — the SWITCH measure feeds the color

Color the agencies table (exercise 2.7) without writing a single rule by hand — the color comes from the measure Tier Color you wrote in exercise 1.10.

Format visual ← Cell elements
  Series: Agency 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 a single visual — changing the threshold requires fixing every visual separately. The measure Tier Color It's a single source of truth: one change in the SWITCH code updates everywhere it's used, and you can connect a table, Matrix, chart and card to it.

If the text can't be read on the dark background — also turn on Font color the same way with your own color-text measure.

Both techniques produce exactly the same split: 12 / 30 / 22.

Summary — all the results in one place

A consolidated validation table for all 14 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 / component	Verified result
Total Mass Delivered	35,093,955 kg
Launches Flown	1,500 (COUNT would return 50,794)
Mass per Launch	23395.97
Total Telemetry Packets	23,875,538
Deep Space Mass	2,073,879 (5.91%)
Prime Mass	17,573,747 (50.08%) · Partner 17,520,208
% of Program Mass	Leader 3.42% · Total 100.00%
Agency Status (IF)	30 above · 34 below · average 548343.05
Agency Tier (SWITCH)	12 top · 30 middle · 22 bottom
Agency Rank (RANK)	Perigee Space Group = 1 · Vega Astronautics = 64
Leader Mass (INDEX)	1,199,926 in each row
Running Mass (WINDOW)	Last row = 35,093,955
Total Slot Revenue	122,426,598,258.00 USD
The leading agency	Perigee Space Group — 1,199,926 kg across 78 launches
The heaviest cargo	PLD-1105 — 164,460 kg
peak year	FY2023 — 3,768,619 kg
The top cargo department	Satellite — 30.3% of the mass

All results were verified in pandas against Space_PowerBI_Model.xlsx (50,794 fact rows), and all 44 relationships were checked programmatically — 0 orphan keys, 0 blank keys, 0 circular dependencies and 0 duplicate filter paths.