Educational institution model — assignment prep and structure
Two dashboard pages where every number comes from a measure you write from scratch, with automatic color indication
for plan status. Working on Education_PowerBI_Star.xlsx - a pure star schema: one fact table,
seven dimensions, 28,640 rows, and seven relationships. 29 exercises, each on a separate page with a full solution. Note: the download
and practice are On the computer only — Power BI Desktop doesn't exist on phone.
50,294,057dollars of tuition collected — the anchor number
28,640Registration rows
4,198students
14Measures to write
The data is synthetic. The city and country names are real, but every student, course, instructor,
program and number in the file were artificially generated with a fixed random seed. Nothing about a real educational institution should be inferred from them.
Before we start — building the model. Load the model's eight tables
(Relationships it's a documentation sheet — don't load it). Turn off Autodetect,
build the seven relationships manually, and make sure they're all One to Many in the direction Single from the dimension to
Fact_Enrollments. Mark the Dim_Date as a date table
(Mark as date table on Full_Date).
The big trap in this file — canceled rows that carry unrealized revenue.
In the fact table there are two money columns that look like twins: Tuition_Billed_USD andTuition_Collected_USD.
for every row whose status belongs toStatus_Group = "Lost" — meaning Withdrawn andCancelled —
there's a positive charge but collection zero. Whoever sums the charge column will get
61,236,519.19 instead 50,294,056.87 — inflation of
10,942,462.32 dollars, 17.9% of the total billed, that never made it to the register.
Throughout the whole assignment, we sum Tuition_Collected_USD. A charge is an intent, collection is revenue.
Matching brackets.Table[Column] = column · [measure] =
square brackets without a table name. For each measure: right-click on Fact_Enrollments 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
Eleven base measures — two SUM, two 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 smoothed line chart, a slicer, a plans table, Top N, Matrix and a combo chart.
Part 3
Conditional formatting and thinking
The same division into three tiers using two techniques, then two thinking exercises: unpacking the trap and the channel-profitability insight.
All the numbers were verified in pandas against the file
1.1 · Total Tuition Collected — the base measure
Create a measure named Total Tuition Collected that summarizes the Fact_Enrollments[Tuition_Collected_USD]. Format with a thousands separator and two decimal digits.
Total Tuition Collected =
SUM (
Fact_Enrollments[Tuition_Collected_USD]
)
creationRight-click on Fact_Enrollments ← New measure
designMeasure tools ← Format: Currency ← 2 digits
checkDrag onto an empty card to make sure the number appears
Why this specific column: In the table also sits Tuition_Billed_USD, and it returns 61,236,519.19. The difference of 10,942,462.32 The dollar is exactly the charge of the canceled rows — money that was recorded as a debt and was never collected. Exercise 4.1 breaks this down completely.
result: 50,294,056.87 dollars — the anchor number of the whole assignment.
1.2 · Students Enrolled — how many students, really
Create a measure named Students Enrolled That counts students unique based on Fact_Enrollments[Student_ID].
Students Enrolled =
DISTINCTCOUNT (
Fact_Enrollments[Student_ID]
)
Why not COUNT:COUNT(Student_ID) would return 28,640 — it counts rows that have a value, not unique values. Each student enrolls in 6.8 courses on average, hence 28,640 ÷ 4,198 ≈ 6.82.
result: 4,198 Students.
1.3 · Tuition per Student — the Lego principle
Create a measure named Tuition per Student — strictly from the two existing measures, without the division sign / and without AVERAGE. Format to two decimal places.
The average of what:AVERAGE(Tuition_Collected_USD) would return the average per enrollment record (1,756.08), not per student. 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 programs table and it will return the tuition per student for each program separately — without writing another line.
result: 11,980.48 Dollar per student.
1.4 · Total Credit Hours — SUM on a different column
Create a measure named Total Credit Hours that summarizes the Fact_Enrollments[Credit_Hours]. The measure serves the combo chart in exercise 2.10.
Total Credit Hours =
SUM ( Fact_Enrollments[Credit_Hours] )
This column is truly derived:Credit_Hours in the fact table always equals Credit_Value of the course in-Dim_Course. You could have skipped it and aggregated through the relationship — it sits in the fact table so you can aggregate without touching the dimension.
result: 481,915 Credit hours.
1.5 · Courses Delivered — a second DISTINCTCOUNT
Create a measure named Courses Delivered that counts distinct courses based on Fact_Enrollments[Course_ID].
Note the difference from 1.2: The same function, a different column, and a result of an entirely different magnitude. DISTINCTCOUNT Always counts the distinct values In the current context — drag the measure to the table by Dim_Campus[Campus_Name] and they'll see how many different courses ran on each campus, not 180 in every row.
result: 180 Courses — the entire catalog is active.
1.6 · Online Tuition — first CALCULATE
Create a measure named Online Tuition that returns the tuition collected from online courses only. Use CALCULATE with [Total Tuition Collected] As an expression and a condition on Dim_Course[Delivery_Mode].
What happens here: CALCULATE adds the condition to the filter context and only then runs the [Total Tuition Collected]. The filter sits on a text column in the dimension table, flows through the relationship to 28,640 fact rows — without any JOIN.
result: 14,331,147.98 dollars — 28.49% of total collection. The full breakdown: Campus 24,521,003.34 · Online 14,331,147.98 · Hybrid 11,441,905.55
1.7 · Realized Tuition — CALCULATE with KEEPFILTERS
Create a measure named Realized Tuition that returns only the collection from records that ended — Dim_Status[Status_Group] = "Realized". Wrap the condition inKEEPFILTERS.
What KEEPFILTERS changes: A regular condition inside CALCULATE overrides Every filter that exists on that same column. KEEPFILTERS Replaces the override with an intersection. The test: put a slicer on Status_Group and choose Open only. Without KEEPFILTERS the measure will keep returning 35,487,304.63 and ignore the slicer; with KEEPFILTERS it will return empty — because the intersection of Open with Realized is an empty set. That's the correct answer.
Why this matters especially here: The third group, Lost, holds a collection zero but a charge of 10,410,534.43 dollars. The breakdown by status is exactly where the trap in the file is exposed to the eye.
result: 35,487,304.63 dollar. By status group: Realized 35,487,304.63 · Open 14,806,752.24 · Lost 0.00
1.8 · % of All Programs — ALL releases the filter
Create a measure named % of All Programs that returns each plan's share out of all rows Dim_Program — even when the visual is filtered to one plan. Format as a percent with two decimal places.
% of All Programs =
DIVIDE (
[Total Tuition Collected],
CALCULATE (
[Total Tuition Collected],
ALL ( Dim_Program )
)
)
Where ALL works: The numerator is calculated in row context — the tuition of the current program. The denominator is wrapped inCALCULATE with ALL ( Dim_Program ), which removes the filter on all the dimension's columns and always returns the total of all 24 programs. If you had written ALL ( Dim_Program[Program_Name] ) You'd only release one column — filter on Faculty_Name would keep truncating the denominator.
The sanity test: Drag the measure to the plans table. The percentage column must sum to exactly 100.00%. If it doesn't — that's a sign your denominator is filtered.
result: Public Health = 5.20% (2,613,332.23) · Digital Marketing = 2.88% (1,449,455.63) · column total = 100.00%
1.9 · Program Status — IF vs. a fixed target
Create a measure named Program Status that returns "Above Target" When the program's tuition reaches the annual target of 2,100,000 dollars, and"Below Target" otherwise.
Program Status =
IF (
[Total Tuition Collected] >= 2100000,
"Above Target",
"Below Target"
)
A fixed target, not an average: The target here is a pre-agreed number set in the budget, not a value calculated from the data. The advantage: the result is stable and doesn't move when you filter the visual. The disadvantage: if you change the target you'll have to edit the formula — in exercise 2.6 you'll see how a What-if parameter solves this.
A text measure isn't a numeric measure: You cannot rank it, sum it, or feed it into a chart. Its place is in a table and a card only.
result: 14 Plans above target · 10 below it. The first program below the target: Social Work (1,995,665).
1.10 · Program Tier — SWITCH for three tiers
Create a measure named Program Tier that divides the plans into three tiers: "Top" from2,400,000 and above, "Mid" from1,900,000 and above, and the rest "Low". Use -SWITCH(TRUE()).
Why SWITCH(TRUE()) and not nested IF:TRUE() as the first argument turns SWITCH into a list of conditions that are checked Top to bottom, and the first one that's true wins. Order is critical: had the Mid row come first, every program above 2.4 million would have gotten Mid, since they also pass the lower threshold.
The last argument without a condition It is the default. Without it, a row that does not meet any condition would return blank, and the visual would show an empty row instead of a label.
result: 6 Top plans · 13 Mid · 5 Low. 24 in total — each program received exactly one label.
1.11 · Tier Color — a SWITCH that returns a color
Create a measure named Tier Color that translates the [Program Tier] For the hex code: Top → #132449, Mid → #F5C518, and a default #A9B4CC. This is SWITCH on a value, not on TRUE().
Two forms of the same function: In the previous exercise, SWITCH received TRUE() and compared conditions. Here it receives expression and compares it to values. This form is shorter and more readable when there's a closed list of values.
This measure is not meant to be seen. It feeds the conditional formatting pane in exercise 3.2. If you drag it into a table you'll see hex strings — that's expected.
The three parts:DENSE Determines that after a tie the next rank is the next in line, not skipped · ALLSELECTED Defines the window — all the programs the slicer leaves visible · ORDERBY determines what to sort by. Without ORDERBY, RANK will sort by column order and return an alphabetical ranking.
Why ALLSELECTED and not ALL: with ALL The ranking would ignore the slicers and always range between 1 and 24. With ALLSELECTED Filtering to a single faculty re-ranks within that faculty — number 1 will be the leader within the current selection.
result: Public Health = 1 · Digital Marketing = 24. 24 programs, 24 rankings — there's no tie in this file, so DENSE and SKIP return the same thing here. The difference will show up once you filter to a faculty with identical values.
1.13 · Leader Tuition — INDEX returns the leader
Create a measure named Leader Tuition that returns, in every row, the tuition of the program the leader — the same number in every row of the table. UseINDEX with ORDERBY.
INDEX returns a row, not a number. It picks the row at the position you requested within the window — 1 = the first one after sorting. To turn a row into a number, wrap it inCALCULATE, that uses the selected row as a filter and runs the measure on it. INDEX ( -1, ... ) would return the last one.
What to do with it: A natural helper measure is DIVIDE ( [Total Tuition Collected], [Leader Tuition] ) — percent of the leader. For Public Health it will return 100%, and for Digital Marketing 55.46%.
result: 2,613,332.23 — the same number in all 24 rows. If it changes from row to row, your window isn't ALLSELECTED.
1.14 · Tuition Moving Avg 3M — WINDOW
Create a measure named Tuition Moving Avg 3M that returns a three-month moving average — the current month and the two months before it. UseWINDOW with REL.
The first four numbers:-2, REL = lower bound, two rows back relative to the current row · 0, REL = upper bound, the current row itself. REL = relative to the current row; ABS It would count from the start of the window.
Why AVERAGEX and not AVERAGE:WINDOW returns table of up to three rows. AVERAGEX runs over it row by row, calculates the measure on each one, and only then averages. Switching toSUMX will give a running sum instead of a moving average.
The first two rows differ: In January 2023 there's one row in the window, and in February two, so the average there is of fewer than three months. This is correct behavior, not a bug — anyone who wants a blank in these rows must explicitly count them.
Display by Dim_Date[Year_Month] (36 months). result: 2023-03 = 2,059,545.77 · 2025-12 = 1,593,617.98 (the month itself: 817,666.91 — the moving average softens the semester dip).
2.1 · Background, title and KPI tray — the layout
Build page 1 of the dashboard: dark background, title, and a tray to host the four cards. Still no visuals — just the skeleton.
titleInsert ← Text box ← "Education Performance Overview" ← Segoe UI 28 · white
subtitleA second box, 13pt, in color #A9B4CC: "Tuition collected, FY2023-FY2025"
trayInsert ← Shapes ← Rectangle ← fill #1B3060 ← rounded corners 8 ← send to back
the page nameDouble-click the page tab ← Overview
Why a tray and not four separate backgrounds: One shape behind four cards gives even spacing and is easy to move. Four separate backgrounds start to drift the moment you move a single visual.
Pay attention to the grid: activate View → Gridlines andSnap to grid before you assume anything. A layout built by eye will look skewed at any other resolution.
At the end of the exercise there's a dark page with a title, subtitle, and an empty rectangle the width of the dashboard. No visual yet.
2.2 · Images — search, add and position
Add a logo in the top corner and a subtle background image for the title area, without hurting readability.
sourceLook for a free-to-use image (Unsplash / Pexels) on the topic of campus or library
additionInsert ← Image ← Choose the file
matchFormat image ← Scaling: Fit So as not to distort the ratio
transparencyFor the background image: added a #132449 rectangle over it at 70% opacity
orderSelection pane ← Drag the image below all the text
accessibilityGeneral ← Alt text ← A short description for a screen reader
Rule of thumb: An image in a dashboard is background, not content. If it draws the eye before the numbers do — lower its opacity further. A contrast ratio of 4.5:1 between text and background is the minimum.
The file size goes into the PBIX: A 4MB image bloats the file and slows down opening. Compress it to 200KB before adding it.
At the end of the exercise there's a small logo in the corner and a dimmed background image behind the title, and the title is still clearly readable.
2.3 · The four cards — measures only
Place four cards on the tray: [Total Tuition Collected], [Students Enrolled], [Courses Delivered] and[Tuition per Student]. You must not drag a raw column into either of them.
additionVisualizations ← Card ← Drag the measure toFields
titleFormat ← Callout value ← 32pt · white
labelCategory label ← 12pt · #A9B4CC
alignmentSelect all four ← Format ← Align top ← Distribute horizontally
backgroundTurn off the background of each card — the tray from exercise 2.1 is the background
Why not a raw column: A card that takes a column shows First or Count based on the type, and the moment someone filters it may return a number that has no meaning. An explicitly defined measure rolls up correctly in every context.
The four cards show: 50,294,056.87 · 4,198 · 180 · 11,980.48.
2.4 · Column chart — tuition by program
built Clustered bar chart with Dim_Program[Program_Name] on the axis and[Total Tuition Collected] in the values, sorted from highest to lowest.
labelsFormat ← Data labels ← On ← Display units: Millions
axisTurn off the axis title — the visual's name already says what it is
Drag the name from the dimension, not from the facts. inFact_Enrollments there's only Program_ID. A visual built on the key will show codes instead of names, and won't respond to a slicer on Faculty_Name.
Bar and not Column: 24 long program names on a horizontal axis get cut off or rotated diagonally. Horizontal bars give the name a full row.
The top plan: Public Health — 2,613,332.23. The bottom one: Digital Marketing — 1,449,455.63.
2.5 · Smoothed line chart — the monthly trend
built Line chart with Dim_Date[Year_Month] on the axis, [Total Tuition Collected] and[Tuition Moving Avg 3M] as two lines. Enable smoothing on the main line.
visualLine chart
X axisDim_Date[Year_Month] — make sure it Categorical and not Date hierarchy
values[Total Tuition Collected] and [Tuition Moving Avg 3M]
smoothingFormat ← Lines ← Shape ← Smooth line: On
colorsMain line #F5C518 with width 3 · Moving average #A9B4CC with width 2, dashed
sortingSort axis ← Year_Month ← Ascending
Seasonality you'll see right away: September-October and January-February spike, July-August plunge. This is the academic calendar, not noise. Total: Spring 24,763,017 · Fall 21,537,164 · Summer 3,993,875.
The smoothing lies a little. Smooth line draws a curve between points and therefore invents intermediate values that don't exist. It's nice for trend, and risky for precise reading — that's why the moving average stays a straight line.
The smoothed line starts at2,568,361.43 (2023-01) and ends with817,666.91 (2025-12). The moving average at the last point: 1,593,617.98.
2.6 · Page 2 and the study-year slicer
Create a second page named Detail, copy the tray and the title to it, and add a slicer on Dim_Date[Academic_Year] that synchronizes both pages.
page+ at the bottom ← name: Detail ← same background and same title
synchronizationView ← Sync slicers ← check Sync and Visible on both pages
DefaultLeave the slicer empty — empty means everything
What-ifModeling ← New parameter ← Numeric range ← Target · 1,000,000 to 3,000,000 · step 100,000
The parameter releases the target from 1.9: Replace with a formula Program Status the number 2100000 in[Target Value], and the user will move the slider and see how many programs pass the target in real time.
Note the sync:Sync without Visible Filters the second page without showing the slicer there — a confusing situation that leads users to report "wrong numbers".
choice of AY2024 Lowers the first card to 16,814,633.91. The three years: AY2023 16,802,711.36 · AY2024 16,814,633.91 · AY2025 16,676,711.60 — their sum is exactly the anchor number.
2.7 · Programs Table — Where IF and SWITCH Live
on the page Detail built Table with Dim_Program[Program_Name], Dim_Program[Faculty_Name] and the five measures: [Total Tuition Collected], [% of All Programs], [Program Rank], [Program Status] and[Program Tier].
visualTable — not a Matrix, there's no hierarchy here
columnsTwo dimension columns and then the five measures in order
sortingClick on the header Program Rank ← ascending
sumFormat ← Totals ← On — make sure % sums to 100.00%
widthDrag the column boundaries manually; don't rely on auto-fit
The additivity test: The summary row of [Total Tuition Collected] must show 50,294,056.87 and of [% of All Programs] Exactly 100.00%. [Program Rank] In the summary row it will show 1 — that's correct, because in the summary context there's no plan to rank.
What not to put here: the Revenue_Per_Credit_Hour from the fact table. It's a row-level ratio, and Power BI will sum it by default and show a completely meaningless number. A ratio should always be calculated as a DIVIDE measure at the summary level.
The first row: Public Health · Faculty of Health · 2,613,332.23 · 5.20% · 1 · Above Target · Top.
2.8 · Top 10 Courses — Bar with a Top N Filter
Build a bar chart of the top ten courses by [Total Tuition Collected], using a filter Top N and not manual sorting.
visualClustered bar chart ← axis: Dim_Course[Course_Name]
values[Total Tuition Collected]
filterFilters ← Course_Name ← Filter type: Top N
definitionShow items: Top ← 10 ← By value: [Total Tuition Collected]
appliedApply filter — without this click nothing happens
Top N is a dynamic filter. The moment AY2024 is selected in the slicer, the list will be recalculated and the top ten may change. Manual sorting with "Show top 10" wouldn’t do that.
Note the illusion of centrality: The top ten courses together hold only 11.47% from collection. In a catalog of 180 courses there's no real long tail — a fairly flat distribution, and that's an insight in itself.
The leading course: Finance Practice — 634,376.62. Total of the top ten: 11.47% of50,294,056.87.
2.9 · Matrix — faculty × teaching mode
built Matrix with Dim_Program[Faculty_Name] in rows, Dim_Course[Delivery_Mode] in columns and[Total Tuition Collected] in values.
visualMatrix
rowsDim_Program[Faculty_Name]
columnsDim_Course[Delivery_Mode]
values[Total Tuition Collected]
sumsFormat ← Row subtotals andColumn subtotals ← On
unitsFormat ← Display units: Millions ← one digit
Two different dimensions on the edges of one matrix. that's exactly what a star schema gives: Dim_Program andDim_Course don't know each other, but both filter the same fact table, so the intersection between them is computed automatically.
The bottom-right corner test: The total sum of the matrix must be 50,294,056.87. If it's bigger — you have a bidirectional relationship or a double filter path in the model.
built Line and clustered column chart: columns = [Total Tuition Collected], line = [Total Credit Hours], axis = Dim_Course[Subject_Area].
visualLine and clustered column chart
axisDim_Course[Subject_Area]
columns[Total Tuition Collected]
line[Total Credit Hours] ← in the Line y-axis area
second axisFormat ← Y-axis (secondary) ← On — without it the line will be crushed
sortingBy [Total Tuition Collected] descending
Two axes = two units of measure. Dollars and credit hours are not the same order of magnitude. Without a secondary axis the line will hug the bottom and you won't see anything.
what to look for: An area where the bar is high and the line is low charges a lot of money for few hours - meaning a high rate per billable hour. The opposite area is teaching-intensive. This foreshadows the closing insight in exercise 4.2.
The sum of the line across all domains: 481,915 Credit hours. Total of the columns: 50,294,056.87 dollar.
2.11 · Navigation and polish — closing the dashboard
Connect both pages with navigation buttons, and go over the final polish checklist.
Alt textA short description for every visual, in General ← Alt text
The final polish checklist: There's no visual with a default title (Total Tuition Collected by Program_Name) · no scroll bar inside a visual at the standard size · all numbers with the same number of digits · no two shades of the same blue · no visual cut off at the edge of the canvas.
Closing check: Click on a column in the plans chart and confirm all other visuals respond. If one of them doesn’t move — it’s built on a column from the fact table instead of from the dimension.
By the end of the exercise: two linked pages, every visual with a title, and every number on the dashboard comes from a measure you wrote.
3.1 · Rules — conditional formatting within a single visual
Color the column chart from exercise 2.4 in three layers using Rules — without using the color measure.
openingChoose the chart ← Format ← Bars ← Colors ← fx
typeFormat style: Rules
fieldBased on field: [Total Tuition Collected] ← Summarization: Sum
rule 1If value >= 2400000 and < 999999999 ← #132449
rule 2If value >= 1900000 and < 2400000 ← #F5C518
rule 3If value >= 0 and < 1900000 ← #A9B4CC
The order in the pane is the order that's checked. Power BI stops at the first rule that's true, exactly like SWITCH(TRUE()). A rule that covers too wide a range at the top of the list will swallow all the rest.
The limitation of Rules: They live inside this visual only. If you want the same breakdown in the table, in the Matrix, and in the card — you'll need to define it three times and keep it in manual sync. That's exactly the problem exercise 3.2 solves.
Six dark blue columns · thirteen yellow · five gray. The exact same breakdown as [Program Tier] In exercise 1.10.
3.2 · Field value — the SWITCH measure feeds the color
Color the programs table from exercise 2.7 with the same breakdown, this time using Field value and the measure [Tier Color] From exercise 1.11.
openingChoose the table ← Format ← Cell elements
fieldApply to: [Total Tuition Collected]
typeBackground color ← On ← fx
modeFormat style: Field value
measureWhat field should we base this on: [Tier Color]
returnRepeat the same procedure on the bar chart — same measure, zero new settings
This is the fundamental difference: In Rules the logic lives in the visual; in Field value it lives in the model. Changing one threshold in a measure Program Tier Updates all at once every visual that usesTier Color. This is why we wrote two separate SWITCH measures — one for the logic and one to translate it into a color.
What must the measure return: A valid seven-character hex string or a recognized color name. A measure that returns blank gives a cell with no color — not an error, just an absence.
The table and the bar chart now show exactly the same breakdown, from two completely different sources — and that is the check that the measure is correct.
4.1 · The trap — explain why the naive answer is wrong
In this exercise, you don't write a measure. A fellow student built a measure Total Revenue = SUM ( Fact_Enrollments[Tuition_Billed_USD] ), got 61,236,519.19 dollars, and presents the number to management as "the institution's revenue". Write in three to four lines what exactly is wrong here, how big the gap is, and what check in the model would have exposed it within a minute.
The full answer — what is wrong: the column Tuition_Billed_USD documents charge, not collection. Two status groups out of three — Withdrawn andCancelled, which together are Status_Group = "Lost" — carry a positive charge and zero collection. The registration was cancelled, the money never came in, but the charge row remained in the fact table. A measure that sums up charges counts this money as if it were received.
How big is the gap:61,236,519.19 less 50,294,056.87 = 10,942,462.32 dollars — 17.9% of total billing, all attributed to one group. The breakdown by status group: Realized 35,487,304.63 Billed and collected in full · Open 15,338,680.13 charged against 14,806,752.24 Charged · Lost 10,410,534.43 charged against 0.00 that was charged.
The check that reveals this in a minute: Build a Matrix with Dim_Status[Status_Group] in rows and two measures in values — total charged versus total collected. A row where one column is greater than zero and the other is zero is a red flag and impossible to miss. The general rule: Whenever two money columns in a working table look like twins, break both of them down by the status dimension before you choose one.
What was legitimate: Show the two numbers side by side and call them by name — "billed" and "collected" — and add a third measure for the gap. This turns the trap into an operational insight about the churn rate.
the gap: 10,942,462.32 dollars · 17.9% of the charge · all from the group Lost.
4.2 · The closing insight — which teaching channel is really profitable
Build a table with Dim_Course[Delivery_Mode] in rows, and three measures: [Total Tuition Collected], Net Margin = SUM ( Fact_Enrollments[Net_Margin_USD] ) andMargin % = DIVIDE ( [Net Margin], [Total Tuition Collected] ). And then answer: which teaching channel should the institution expand, and why isn't it the largest channel?
The table you'll get:
Profitability by teaching method
Delivery Mode
Tuition collected
net profit
Profit rate
Campus
24,521,003.34
2,150,521.44
8.77%
Online
14,331,147.98
4,509,735.15
31.47%
Hybrid
11,441,905.55
2,392,673.81
20.91%
total
50,294,056.87
9,052,930.40
18.00%
the insight: Campus is the biggest channel — 48.8% of revenue collected — but it only generates 23.8% of the profit, at a rate of 8.77%. Online charges 41% less than it and generates More profit in absolute values: 4,509,735 against 2,150,521. The channel that looks most important in the bar chart is actually the least profitable.
Why this happens — an explanation from the data, not a guess: Two forces act in the same direction. First, Contact_Minutes A credit-hour in an online course stands at 72% of that of an on-campus course. Second, the cost of a minute of teaching on campus is 12% higher than that of an online minute — classrooms, technicians, commuting. Fewer minutes, each of which is cheaper. On the other hand, an online course charges a 14% lower rate per credit-hour — and that still isn't enough to close the gap.
What this means for management: A decision to expand Online isn't a decision about size but about mix. But before recommending, — pay attention to what the data no Say this: there's no measurement here of learning quality, satisfaction, or completion rate by channel. A profitability analysis is only one side of the decision, and it's worth stating that explicitly on the slide.
An extension for those who want more: Add a column to the table Dim_Program[Program_Level] and see whether the gap between the channels holds at every plan level or is concentrated at one level. This is the test that separates a real insight from Simpson’s paradox.
Online — a 31.47% profit margin, and absolute profit 110% higher than Campus's, even though it charges 41% less.
Capstone assignment · Educational Institution
Summary — all the results in one place
A consolidated validation table for all 14 measures and every anchor number 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
1.1 Total Tuition Collected
50,294,056.87
1.2 Students Enrolled
4,198 (COUNT would return 28,640)
1.3 Tuition per Student
11,980.48
1.4 Total Credit Hours
481,915
1.5 Courses Delivered
180
1.6 Online Tuition
14,331,147.98 (28.49%)
1.7 Realized Tuition
35,487,304.63 · Open 14,806,752.24 · Lost 0.00
1.8 % of All Programs
Leads 5.20% · column total 100.00%
1.9 Program Status
14 above target · 10 below · target 2,100,000
1.10 Program Tier
6 Top · 13 Mid · 5 Low
1.11 Tier Color
6 × #132449 · 13 × #F5C518 · 5 × #A9B4CC
1.12 Program Rank
Public Health = 1 · Digital Marketing = 24
1.13 Leader Tuition
2,613,332.23 in all 24 rows
1.14 Tuition Moving Avg 3M
2023-03 = 2,059,545.77 · 2025-12 = 1,593,617.98
The total charge (the trap)
61,236,519.19 — a gap of 10,942,462.32
Net Margin
9,052,930.40 (18.00%)
The leading plan
Public Health — 2,613,332.23
The leading course
Finance Practice — 634,376.62
The leading campus
Distance Learning Hub — 5,832,708.10
peak year
AY2024 — 16,814,633.91
The most profitable channel
Online — 31.47% profit margin
All results were verified in pandas against Education_PowerBI_Star.xlsx (28,640 fact rows), and all seven relationships were checked programmatically — 0 orphaned keys, 0 blank keys, 0 circular dependencies, and 0 duplicate filter paths. The two derived columns in the fact table were verified against their formula row by row.
You can also navigate with the buttons at the top of the screen, keyboard arrows, and on phone with a swipe