Champions League Summary Assignment — Star Schema in Power BI

Capstone assignment · Champions League

Champions League model — assignment prep and structure

Two dashboard pages where every number comes from a measure you write from scratch, with automatic color indication for club status. Working on UCL_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.

Downloading the dataset — UCL_PowerBI_Model.xlsx 32 tables · 100,000 rows · 44 relationships · 5.7MB · For download on computer only

2,970Goals — the anchor number

50,799Player-game rows

1,500games

14Measures to write

The data is synthetic. The names of the clubs, stadiums, and cities are real, but every player, result, goal, and amount in the file were generated artificially. Nothing should be inferred from them about the real Champions League — and it's worth saying this explicitly to the class, because someone will always try to cross-check against their own memory.

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), 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.

A gap that's not a bug: inDim_Date There are 4,383 dates, but the games table uses only 267 of them, and only 8 months a year. There are no games in January or June–August — those are the off-season months, and that's what creates the gaps in the line chart.

Matching brackets. Table[Column] = column · [measure] = square brackets without a table name. For each measure: right-click on Fact_PlayerMatchStats in the panel Data ← New measure ← the formula ← Enter.

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 season slicer, a clubs table, Top 10 scorers, 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 Goals — the base measure

Create a measure named Total Goals that summarizes the Fact_PlayerMatchStats[Goals]. Format with a thousands separator.

Total Goals = SUM ( Fact_PlayerMatchStats[Goals] )

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

The most important trap in this file: SUM ( Fact_Matches[Total_Goals] ) Returns 3,051 and not 2,970. The difference is 81 Own goals, credited to the opposing side but not attributed to any player — exactly like in real data. Both numbers are correct, they simply answer two different questions: "how many goals were scored in the matches" versus "how many goals players scored". This is a great exercise for showing why you have to define a metric before arguing about a number.

result: 2,970 Goals — the anchor number of the entire assignment.

1.2 · Matches Played — how many matches actually

Create a measure named Matches Played that counts games unique based on Fact_PlayerMatchStats[Match_ID].

Matches Played = DISTINCTCOUNT ( Fact_PlayerMatchStats[Match_ID] )

Why not COUNT: COUNT(Match_ID) would return 50,799 — it counts rows that have a value, not unique values. Every game is represented by 33 to 34 player rows (the roster of both teams), so 50,799 ÷ 1,500 ≈ 33.9.

result: 1,500 games.

1.3 · Goals per Match — the Lego principle

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

Goals per Match = DIVIDE ( [Total Goals], [Matches Played] )

The average of what: AVERAGE(Fact_PlayerMatchStats[Goals]) would return 0.06 — an average per player-game row, not per game. When the measure is built from two existing measures, the denominator is explicitly defined and you know exactly what you divided. DIVIDE preferred over / 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 clubs table and it will return the rate for each club separately — see the insight in exercise 2.4.

result: 1.98 Goals per game.

1.4 · Total Assists — the second measure

Create a measure named Total Assists that summarizes the Fact_PlayerMatchStats[Assists]. The measure serves the combo chart in exercise 2.10.

Total Assists = SUM ( Fact_PlayerMatchStats[Assists] )

result: 9,169 Steals (6.11 per game).

1.5 · Knockout Goals — first CALCULATE

Create a measure named Knockout Goals that returns the goals in the knockout stages only, with CALCULATE and a condition on Dim_Stage[Stage_Type].

Knockout Goals =
CALCULATE ( [Total Goals], Dim_Stage[Stage_Type] = "Knockout" )

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

result: 671 — 22.59% of all goals. The breakdown by stage: Final 20 · Group Stage 2,299 · Quarter-Final 189 · Round of 16 370 · Semi-Final 92

1.6 · Home Goals — CALCULATE on another fact table

Create a measure named Home Goals that returns the goals of the home team. note: inFact_PlayerMatchStats There's no home/away column — it's in Fact_ClubMatchStats[Home_Away].

Home Goals =
CALCULATE (
    SUM ( Fact_ClubMatchStats[Goals_For] ),
    Fact_ClubMatchStats[Home_Away] = "Home"
)

Why not on the players table: The information simply doesn't exist there. It's in Fact_ClubMatchStats, with one row per team per game. 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.

There's a real byte advantage here: 58.8% of goals go to the home team, and 44.2% of games ended in a home win (draw 28.5%, away win 27.3%).

Sanity check: 1,794 + 1,257 = 3,051. Note that the whole here is 3,051 and not 2,970, because this table does include own goals.

result: 1,794 — 58.8% (outside: 1,257).

1.7 · % of Competition Goals — ALL Releases the Filter

Create a measure named % of Competition Goals: Divide the [Total Goals] in the total goals of the whole competition — CALCULATE that removes the filter from Dim_Club using ALL. Format as a percentage, two digits.

% of Competition Goals =
DIVIDE (
    [Total Goals],
    CALCULATE ( [Total Goals], ALL ( Dim_Club ) )
)

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

Result: the leading club (Dynamo Kyiv) holds 3.16% · The Total row shows 100.00%.

1.8 · Club Status — IF vs. the competition average

Create a measure named Club Status that returns text: "above average" if the club scored 46 goals or more, and otherwise "below average".

Club Status =
IF ( [Total Goals] >= 46, "Above average", "Below average" )

Where does 46 come from: 2,970 ÷ 64 = 46.41 — average goals per club. The measure returns text, so it fits a table, not a numeric card.

Bonus question: Three clubs are tied at exactly 46 goals. Whoever writes > instead >= will get 27 instead of 30 — exactly the kind of mistake worth catching in class.

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

1.9 · Club Tier — SWITCH for three tiers

Create a measure named Club Tier that classifies each club: "top" from 60 goals and up, "middle" from 40 and up, and otherwise "bottom". Use the template SWITCH ( TRUE (), … ).

Club Tier =
SWITCH (
    TRUE (),
    [Total Goals] >= 60, "Top",
    [Total Goals] >= 40, "Middle",
    "Bottom"
)

Order determines: SWITCH stops at the first condition that holds true. If ≥ 40 is written first, all top-tier clubs will be classified as "Middle" and the top tier will remain empty. The last argument, with no preceding condition, is the default.

result: 12 "top" · 31 "middle" · 21 "bottom".

1.10 · Tier Color — a SWITCH that returns a color

Create a measure named Tier Color That returns a HEX code by [Club Tier]: top #132449, middle #F5C518, default #A9B4CC. Here's the regular SWITCH pattern, without TRUE().

Tier Color =
SWITCH (
    [Club 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 by exact match. One trailing space at the end of a string will fail silently, with no error message, and all the clubs will get the default color. 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 · Club Rank — RANK

Create a measure named Club Rank that ranks the clubs by [Total Goals] Using RANK on ALL ( Dim_Club[Club_Name] ), with ORDERBY in descending order. Requires Power BI from the 2023 version onward.

Club Rank =
RANK ( ALL ( Dim_Club[Club_Name] ), ORDERBY ( [Total Goals], DESC ) )

What ALL does: removes the row filter and returns all 64 clubs to the list of contenders — without it, in a single club'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 club 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: Dynamo Kyiv = 1 · Lazio = 64.

1.12 · Leader Goals and % of Leader — INDEX

Create a measure named Leader Goals that returns in every row the goals of the first club in the list — CALCULATE with INDEX ( 1, … ). bonus: measure % of Leader — DIVIDE of the base measure by [Leader Goals], as a percent with one digit.

Leader Goals =
CALCULATE (
    [Total Goals],
    INDEX ( 1, ALL ( Dim_Club[Club_Name] ), ORDERBY ( [Total Goals], DESC ) )
)

% of Leader = DIVIDE ( [Total Goals], [Leader Goals] )

A fixed money column — the leader's value repeats on every row
club	Total Goals	Leader Goals	% of Leader
Dynamo Kyiv	94	94	100.0%
Liverpool FC	78	94	83.0%
Celtic FC	77	94	81.9%
Bologna FC	76	94	80.9%
Lazio	13	94	13.8%

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: 94 gates in each row · the last one stands at 13.8% of it.

1.13 · Running Goals — WINDOW

Create a measure named Running Goals — 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 Goals =
CALCULATE (
    [Total Goals],
    WINDOW ( 1, ABS, 0, REL,
        ALL ( Dim_Club[Club_Name] ),
        ORDERBY ( [Total Goals], DESC )
    )
)

The window grows row by row up to the competition total
club	Total Goals	Running Goals
Dynamo Kyiv	94	94
Liverpool FC	78	172
Celtic FC	77	249
Sevilla FC	20	2,941
Athletic Club	16	2,957
Lazio	13	2,970

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 my own position. 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 2,970. If not, something in the window is broken.

1.14 · Total Attendance — the third fact table

Create a measure named Total Attendance that summarizes the Fact_Matches[Attendance]. Format with Display units = Millions anddecimal places = 1.

Total Attendance = SUM ( Fact_Matches[Attendance] )

Why this matters: The measure proves that the model connects three fact tables under the same slicers. Select a season in the slicer — goals, assists, and attendance will update together.

note: Attendance_Pct stored in the file as a fraction (0.71–0.99).\n Format it as a percentage in Power BI, don't multiply by 100.

result: 68,442,087 Viewers · average 45,628 per game.

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 very light version of the navy ← Transparency = 0%
titleInsert ← Text box ← "Champions League — the competition's situation room"
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

Two image files will turn the report from an assignment into one that looks professional.

searchSoccer ball or trophy 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 UEFA logo or a club logo. These are registered 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 Goals · Matches Played · Goals per Match · Total Attendance

The four cards and the verified values
card	the measure	The value to display
total goals	Total Goals	2,970
games	Matches Played	1,500
Goals per game	Goals per Match	1.98
total audience	Total Attendance	68.4M

Two display traps: (1) Matches Played must Display units = None, otherwise "1.5K" will show instead of "1,500". (2) On the audience 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 — goals by club

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

X-axis = Dim_Club[Club_Name]
Y-axis = [Total Goals]     ← the measure, not the column
Sort   = Total Goals, descending

Six leaders and three trailing
#	club	goals	% of the competition
1	Dynamo Kyiv	94	3.16%
2	Liverpool FC	78	2.63%
3	Celtic FC	77	2.59%
4	Bologna FC	76	2.56%
5	AZ Alkmaar	72	2.42%
6	Arsenal FC	70	2.36%
62	Sevilla FC	20	0.67%
63	Athletic Club	16	0.54%
64	Lazio	13	0.44%

The dashboard's key insight — and a classic interpretation trap: Dynamo Kyiv leads with 94 goals and Lazio closes with 13 — a gap of 7.2x. But drag the [Goals per Match] to the same table and you'll get 1.08 vs. 1.08 — Exactly the same pace. The whole difference is participation: 87 games versus 12, because not every club enters the competition every season (2 to 10 seasons). This is exactly the difference between a cumulative measure and a ratio measure, and that's why a dashboard with totals alone can be completely misleading.

the leader: Dynamo Kyiv — 94 goals (3.16% of the competition).

2.5 · Line chart — goal trend over time

built Line chart with Dim_Date[Full_Date] on the X axis and[Total Goals] 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 Goals]

Goals by season
season	Total Goals
2014/15	230
2015/16	242
2016/17	258
2017/18	240
2018/19	257
2019/20	242
2020/21	292
2021/22	243
2022/23	224
2023/24	252
2024/25	244
2025/26	246

What we see: A peak in the 2020/21 season with 292 goals, a low in 2022/23 with 224. At the monthly level: 8 months with games only, a peak in October with 777 goals.

The gaps in the line are real: the group stage is concentrated in the fall, and there are no games during the break. The monthly curve doesn't reflect a "strong season" but rather the schedule — and that's why you must Mark as date table.

Record: 2020/21 with 292 goals.

2.6 · Page 2 and the Season Slicer

Open a second page named "Clubs and Players Spotlight" with the same background and the same color family.

page＋ at the bottom ← name: Clubs & Players Spotlight ← same canvas background — consistency
slicerSlicer ← field Dim_Season[Season_Label] ← Format ← Slicer settings ← Style = Tile
designStretch 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_Matches and not from the dimension.

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

built Table with Dim_Club[Club_Name] and the seven measures: Club Rank, Total Goals, % of Competition Goals, % of Leader, Running Goals, Club Status, and Club Tier. Sorted descending, Totals = Off.

Dim_Club[Club_Name] · [Club Rank] · [Total Goals]
[% of Competition Goals] · [% of Leader] · [Running Goals]
[Club Status] · [Club Tier]

Four leaders and two last
rating	club	goals	% of the competition	Status	Tier
1	Dynamo Kyiv	94	3.16%	Above average	top
2	Liverpool FC	78	2.63%	Above average	top
3	Celtic FC	77	2.59%	Above average	top
4	Bologna FC	76	2.56%	Above average	top
63	Athletic Club	16	0.54%	Below average	bottom
64	Lazio	13	0.44%	Below average	bottom

The difference between the two measures: Club Status compared to the average calculated from the data (46.41), andClub Tier compares to fixed thresholds that we set. A club can be "below average" and still be 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 clubs "above average" · tier split: 12 / 31 / 21.

2.8 · Top 10 scorers — Bar with a Top N filter

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

Y-axis = Dim_Player[Player_Name]
X-axis = [Total Goals]
Filter = Top N, 10, by [Total Goals]

The top ten scorers
#	player	position	goals
1	Mateo Wagner	Second Striker	12
2	Alessio Sorensen	Left Winger	11
3	Andre Hoffmann	Centre Forward	11
4	Aaron Perrin	Left Winger	10
5	Idrissa Zlatko	Right Winger	10
6	Riccardo Escobar	Right Winger	10
7	Andres Rossi	Left Winger	9
8	Erik Peeters	Attacking Midfielder	9
9	Felix Marchetti	Right Winger	9
10	Minjun Brennan	Centre Forward	9

A tie case for the lesson: The value in tenth place is 9 goals, and 5 players are tied on it exactly. A Top N filter with identical boundary values will return more rows than expected — in tie situations you add a tiebreaker (for example a secondary sort by assists or minutes played). This is correct Power BI behavior, not a bug.

the leader: Mateo Wagner (Second Striker) — 12 goals.

2.9 · Matrix — country × tournament stage

built Matrix with Dim_Country[Country] in rows, Dim_Stage[Stage_Name] in columns and[Total Goals] in values. Style presets = Minimal · Cell elements ← Data bars = On.

Rows    = Dim_Country[Country]
Columns = Dim_Stage[Stage_Name]
Values  = [Total Goals]

The top nine countries out of 16
country	houses	eighth	quarter	half	final	total
England	306	52	25	12	1	396
Germany	304	47	25	9	1	386
Italy	280	41	24	18	3	366
Spain	227	45	17	6	3	298
France	207	30	15	13	3	268
Netherlands	142	35	21	8	0	206
Portugal	136	28	6	1	2	173
Ukraine	98	18	11	9	4	140
Belgium	107	10	8	2	0	127

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

Interpretation trap: The finals column is based on just one game per season. Percentages on such a small base are misleading, and that is a lesson in itself.

Sum of all cells: 2,970 goals — identical to the base measure.

2.10 · Combo chart — goals vs. assists by position

built Line and clustered column chart with Dim_Position[Position_Group] on the X axis, [Total Goals] in columns and[Total Assists] in the line. The columns in navy, the line in gold.

X-axis        = Dim_Position[Position_Group]
Column y-axis = [Total Goals]
Line y-axis   = [Total Assists]

Two series, two units of measure
Position group	goals	% of the goals	cooking	% of the cooking
Forward	1,559	52.5%	3,096	33.8%
Midfielder	1,123	37.8%	4,617	50.4%
Defender	288	9.7%	1,437	15.7%
Goalkeeper	0	0.0%	19	0.2%

This is the insight of the whole dashboard: Forwards hold 52.5% of the goals but only 33.8% of the assists, while midfielders are the opposite — 37.8% of the goals and 50.4% of the assists. Same model, same visual, two measures, two stories.

Goalkeepers with 0 goals: This is correct behavior, not a hole in the data. In a regular column chart, this series would be squashed to the bottom — so this is precisely a case where two axes are the right choice, and the rule "don't use two axes" doesn't apply.

the leader: Forward — 52.5% of the goals.

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
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

One last check: hide the title — it should still be immediately clear that this is a report about the Champions League.

3.1 · Rules on the Bar Chart

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

Rule 1:  >= 0   and <  40   ->  #A9B4CC
Rule 2:  >= 40  and <  60   ->  #F5C518
Rule 3:  >= 60              ->  #132449

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

The result: 12 clubs in dark navy, 31 in gold, 21 in light navy.

3.2 · Field value — the SWITCH measure feeds the color

Color the clubs 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: Club 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 is a single source of truth: One change in the SWITCH code updates everywhere it is used.

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 / 31 / 21.

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 Goals	2,970
Total goals in Fact_Matches (including own goals)	3,051 — a difference of 81
Matches Played	1,500 (COUNT would return 50,799)
Goals per Match	1.98
Total Assists	9,169 (6.11 per game)
Knockout Goals	671 (22.59%)
Home Goals	1,794 (58.8%) · away 1,257
% of Competition Goals	Leader 3.16% · Total 100.00%
Club Status (IF)	30 above · 34 below · average 46.41
Club Tier (SWITCH)	12 top · 31 middle · 21 bottom
Club Rank (RANK)	Dynamo Kyiv = 1 · Lazio = 64
Leader Goals (INDEX)	94 in every row · last one 13.8%
Running Goals (WINDOW)	Last row = 2,970
Total Attendance	68,442,087 · average 45,628
The leading club	Dynamo Kyiv — 94 goals in 87 matches
Leading scorer	Mateo Wagner — 12 goals
Peak season	2020/21 — 292 goals
Combo chart	Wingers 52.5% of goals · midfielders 50.4% of assists

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