Telecom Summary Assignment — Star Schema in Power BI

Total Billed Revenue =
SUM ( Fact_Usage[Billed_Amount_USD] )

1. creationRight-click on Fact_Usage ← New measure
2. designFormat ← Currency ← two digits
3. checkDrag onto an empty card to make sure the number appears

Why this is a measure and not a column: A calculated column would be stored once per row out of 30,000 and wouldn't know how to respond to a slicer. A measure is recalculated at every cell of every visual, according to the filter bubble that cell represents. That's the only difference that really matters.

result: 2,594,253.85 USD — the anchor number of the whole assignment.

Total Gross Margin =
SUM ( Fact_Usage[Gross_Margin_USD] )

The column is derived, not invented: Gross_Margin_USD = Billed_Amount_USD - Service_Cost_USD in each of the 30,000 rows. Therefore 2,594,253.85 less 1,704,548.20 Exactly equal 889,705.65. If you get something different — the load broke something, not the model.

Early warning: This total margin already offsets within it 2,824 loss rows. Exercise 3.3 breaks them down.

result: 889,705.65 USD — 34.3% from the income.

Active Customers =
DISTINCTCOUNT ( Fact_Usage[Customer_ID] )

Why not COUNT: COUNT(Customer_ID) would return 30,000 — it counts rows that have a value, not distinct values. Every customer carries 24.3 usage rows in the average, so 30,000 ÷ 1,236 ≈ 24.3.

result: 1,236 Active customers.

Devices In Use =
DISTINCTCOUNT ( Fact_Usage[Device_ID] )

Note the level of detail: The measure counts models, not physical units. Dim_Device contains 48 Models and everyone appear in the fact table, so at the grand-total level the measure returns exactly the number of rows in the dimension. As soon as you filter a region or period the number drops — and that's exactly what makes it a useful measure.

result: 48 Device models.

Revenue per Customer =
DIVIDE ( [Total Billed Revenue],
         [Active Customers] )

The average of what: AVERAGE(Billed_Amount_USD) would return the average for a usage row (86.48 USD), not per customer. When the measure is built from two existing measures the denominator is defined explicitly 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 regions table and it will return the ARPU of each region separately — without writing another line.

result: 2,098.91 USD per customer.

Postpaid Revenue =
CALCULATE (
    [Total Billed Revenue],
    Dim_Plan[Plan_Type] = "Postpaid"
)

What happens here: CALCULATE adds the condition to the filter context and only then runs the [Total Billed Revenue]. The filter sits on a text column in the dimension table, flows through the relationship Dim_Plan[Plan_ID] → Fact_Usage[Plan_ID] and from there to the fact rows — without any JOIN.

result: 1,742,988.22 USD — 67.19% of total revenue.

Core Services Revenue =
CALCULATE (
    [Total Billed Revenue],
    Dim_Service_Type[Service_Name] IN {
        "Mobile Data",
        "Fiber Broadband",
        "IPTV"
    }
)

Why IN and not OR: Three conditions || work on the same column, but they expand into three separate filters that the engine has to unify. IN { } produces a single list of values — shorter, faster, and easy to add a fourth value to without touching the logic.

The trap with curly braces: { } In DAX it’s a table constructor, not a list. If you write a value there that doesn’t exist in the column — you won’t get an error, you’ll simply get less revenue. Check the spelling against Dim_Service_Type before you blame the measure.

result: 1,465,678.21 USD — 56.5% of total revenue.

Pct of All Usage =
DIVIDE (
    [Total Billed Revenue],
    CALCULATE (
        [Total Billed Revenue],
        ALL ( Fact_Usage ),
        ALL ( Dim_Service_Type )
    )
)

Why two ALLs and not one — that's the point of the exercise: ALL ( Fact_Usage ) Clears filters sitting on fact table columns. It no Clears a filter that came fromDim_Service_Type and flows in through the relationship. If you leave only the first-ALL — the numerator and denominator will be identical in every row and the measure will return 100.00% in every row — and this is the most common mistake with this measure.

Immediate sanity check: If the sum of the column in the table isn't exactly 100.00% — the denominator wasn't really released. That's the only check you need.

Result: the leader Mobile Data with 29.61%; the column sums to100.00%.

Margin Status =
VAR BreakEven =
    SUM ( Fact_Usage[Service_Cost_USD] )
VAR Revenue = [Total Billed Revenue]
RETURN
IF (
    Revenue >= BreakEven,
    "Above Break-Even",
    "Below Break-Even"
)

Why VAR and not writing the sum twice: VAR is calculated once per cell and then reused. It's both faster and prevents the classic mistake where someone changes the formula in one place and forgets the other.

The break-even point shifts with the context: At the grand total level it is 1,704,548.20 USD, and in every service row it's the cost of only that service. This is exactly the difference between a measure and a hardcoded number — a hardcoded number would mark everything as profitable or everything as a loss.

result: 8 services above the break-even point and 1 below it (Device Instalment). At the channel level: 8 above, 0 below.

Region Tier =
VAR Revenue = [Total Billed Revenue]
RETURN
SWITCH (
    TRUE (),
    Revenue >= 250000, "Top",
    Revenue >= 150000, "Mid",
    "Low"
)

Why SWITCH ( TRUE ( ) ): It reads the conditions top to bottom and stops at the first one that's true, exactly like a nested IF — but without the nesting. Adding a fourth layer is one line, not re-parsing parentheses.

The order is the logic: If you flip the two rows and put 150000 First, every region above 250,000 will get Mid — because it also satisfies the looser condition. There's no error, just a silent, wrong result.

result: 6 regions Top · 3 Mid · 3 Low.

Tier Color =
SWITCH (
    [Region Tier],
    "Top", "#132449",
    "Mid", "#F5C518",
    "#A9B4CC"
)

Two forms of the same function: In 1.10 the first argument was TRUE () and each pair was a condition-and-result. Here the first argument is an expression, and each pair is a value-and-result - a simple equality comparison. The last, unpaired argument is always the default.

This measure is not meant to be seen: It's meant forConditional formatting ← Field value. Drag it into a table to confirm it returns valid strings, then hide the column. Exercise 3.1 connects it for real.

Result: three valid hex strings — #132449 · #F5C518 · #A9B4CC.

Region Rank by Customers =
RANK (
    DENSE,
    ALLSELECTED ( Dim_Region ),
    ORDERBY ( [Active Customers], DESC )
)

The three parts of RANK: DENSE Ensures there are no gaps in the numbering, ALLSELECTED ( Dim_Region ) defines the comparison group — all the regions the slicer left on screen — andORDERBY Determines what to sort by. Without the second argument, every row will rank itself against itself and get 1.

Why ALLSELECTED and not ALL: ALL It would also ignore the slicer, so a region filtered out would still take up a spot in the ranking. ALLSELECTED Ranks within what the user actually sees — and that's almost always what you want in a dashboard.

result: North Metro instead 1 with 1,087 customers · Highland West instead 12 with 559.

Top Margin Channel Rev =
VAR TopRow =
    INDEX (
        1,
        ALLSELECTED ( Dim_Channel ),
        ORDERBY (
            [Total Gross Margin], DESC
        )
    )
RETURN
CALCULATE (
    [Total Billed Revenue],
    TopRow
)

INDEX returns a row, not a number: INDEX ( 1, ... ) It picks the first row in the sort you defined and returns a single-row table. To get a number from it you need to feed it as a filter into CALCULATE - which is why the measure is built in two steps.

The sort is by interval, the output is revenue: And that's exactly the point. The channel leading by a margin is Web Self Service with 203,233.89 USD margin, while the channel that leads in revenue is Authorised Dealer with 518,340.19 USD. Two different leaders — exercise 3.4 comes back to this.

result: 409,216.29 USD in each row — the revenue of Web Self Service.

Trailing Avg Excl Current =
AVERAGEX (
  WINDOW (
    -3, REL, -1, REL,
    ALLSELECTED ( Dim_Date[Month_Key] ),
    ORDERBY ( Dim_Date[Month_Key], ASC )
  ),
  [Total Billed Revenue]
)

Reading the boundaries: -3, REL it is three rows back from the current row, -1, REL it's one row back. The window stops before the current month, so the average doesn't include it. If you write 0, REL At the upper bound — you got a regular four-month moving average, and that's a different exercise.

Why Month_Key and not Month_Label: WINDOW sorts the rows by the column you gave to ORDERBY, and it must be the same column that appears on the visual's axis. Month_Key it's a number (202401) so it also sorts correctly across years. Put it on the axis.

The first month returns empty: There are no rows before it, and the window is empty. This is correct, not a bug — don't wrap it inCOALESCE Just to see zero.

result: 2025-12 = 130,338.44 USD vs. rolling average 98,001.45 USD. 2024-01 Returns BLANK.

1. page sizeFormat page ← Canvas settings ← 16:9 ← 1280x720
2. backgroundCanvas background ← color #132449 ← transparency 0%
3. titleText box full width at the top of the page — Telecom Performance, font 28, white
4. trayOne rectangle, width 1240 and height 130, below the title, fill #1B2E5A, rounded corners 8
5. Four cardsCard ← one measure per card ← arrange them within the tray with equal spacing
6. alignmentSelect all four ← Format ← Align top ← Distribute horizontally
7. categoryIn every card: Category label On, font 11, color #A9B4CC

Four cards: 2,594,253.85 · 889,705.65 · 1,236 · 2,098.91.

1. donutDonut chart ← Legend = Service_Name ← Values = [Total Billed Revenue]
2. labelsDetail labels ← Label style = Category, percent of total
3. columnsClustered bar chart ← Y axis = Service_Name ← X axis = the same measure
4. sortingIn the column chart: three points ← Sort axis ← the measure ← Descending
5. solid colorBoth visuals with the same palette, so the comparison is about shape, not color
6. the conclusionIn a donut you need to compare angles; in columns the answer is the second row from the top

The leader Mobile Data with 29.61%; the second Roaming Package with 19.78%.

1. base chartClustered column chart ← X axis = Month_Key ← Y axis = [Total Billed Revenue]
2. splitdrag the Market_Cluster to the field Small multiples
3. networkFormat ← Small multiples ← Rows = 1 ← Columns = 3
4. Shared axisY axis ← Shared axis on — without it, comparing between cells is meaningless
5. titlesHeader ← font 12 ← color #F5C518

Three cells: Urban Core, Suburban, Rural — with a shared Y axis.

1. visualScatter chart ← Details = Dim_Channel[Channel_Name]
2. axesX Axis = [Total Billed Revenue] ← Y Axis = [Total Gross Margin]
3. sizeSize = [Active Customers]
4. trend lineAnalytics ← Trend line ← dashed ← #A9B4CC
5. the exceptionThe point furthest below the line is the channel generating revenue that's too expensive

the exception: Authorised Dealer — 518,340.19 USD revenue but only 26.42% gap.

1. visualGauge ← Value = [Total Gross Margin]
2. boundariesFormat ← Gauge axis ← Min = 0 ← Max = 1200000
3. targetTarget value = 900000 — manual, since there's no targets table in the file
4. colorfilling #F5C518 ← target line #A9B4CC
5. labelCallout value on, two digits after the decimal point

the gap 889,705.65 USD vs target 900,000.00 — a gap of -10,294.35 USD (98.9% from the target).

1. target pageCreate a new page ← Format page ← Page information ← Allow use as drill-through
2. Diving fielddrag the Channel_Name to Add drill-through fields here
3. Title cardCard with Channel_Name — so it's clear where you dove into
4. tableTable ← Service_Name, [Total Billed Revenue], [Total Gross Margin], [Margin Status]
5. chartLine chart ← Month_Key against [Total Billed Revenue]
6. Back buttonPower BI automatically adds a back arrow — style it, don't delete it
7. checkOn the main page: right-click the channel column ← Drill through ← Channel Detail

Drilling into Authorised Dealer shows 518,340.19 USD and nine services.

1. new pageFormat page ← Page information ← Allow use as tooltip
2. sizeCanvas settings ← Type = Tooltip ← 320x240
3. contentThree small cards: [Total Billed Revenue], [Total Gross Margin], [Region Tier]
4. background#132449 at 0 transparency — otherwise the panel will look transparent over the chart
5. additionIn the area chart: Format ← Tooltips ← Type = Report page ← Choose the page
6. checkhover over North Metro — three rows should appear

Hovering over North Metro shows 367,098.76 USD and a layer Top.

1. Two visualsA revenue chart and a margin chart, one exactly above the other, with the same coordinates and size
2. SelectionView ← Selection — here you turn the eye on and off for each visual
3. First bookmarkRevenue lit up, margin dim ← Bookmarks ← Add ← name Revenue View
4. Second bookmarkreverse ← Add ← name Margin View
5. importantIn every Bookmark: right-click ← uncheck Data ← Keep only Display — otherwise it will freeze the slicers too
6. buttonssecond Button ← Action ← Bookmark ← choose the appropriate one

A click toggles between 2,594,253.85 USD versus 889,705.65 USD in the same spot on the page.

1. visualWaterfall chart ← Category = Service_Name ← Y axis = [Total Gross Margin]
2. sortingSort axis ← the measure ← Descending — so that donors appear before churners
3. colorsSentiment colors ← Increase = #132449 ← Decrease = #F5C518
4. BreakdownFormat ← Breakdown ← Max breakdowns = 9 — so all nine services appear and aren't grouped into Other
5. readingThe Total column at the end must be exactly equal to[Total Gross Margin]

One column goes down: Device Instalment with -36,122.79 USD. The Total sums to889,705.65.

1. visualTreemap ← Category = Device_Class ← Values = [Total Billed Revenue]
2. breakdownDetails = Device_Vendor — creates inner boxes inside each rectangle
3. labelsData labels on ← Value lit up — without it the Treemap is just a drawing
4. card next toCard with [Devices In Use] — the context changes with the slicer
5. warningTreemap is good at showing who's big and bad at showing by how much. If you need to compare numbers — columns

The big rectangle: Smartphone with 522,228.62 USD. The card: 48 models.

1. slicerSlicer ← Dim_Region[Region_Name] ← Format ← Dropdown
2. synchronizationView ← Sync slicers ← mark Sync andVisible On both pages
3. navigationInsert ← Buttons ← Navigator ← Page navigator
4. alignmentSelect all ← Format ← Align — uneven spacing is what the eye picks up on first
5. cleaningIn every visual: Title short, Background off, Border off, Shadow off
6. Focus modeturn off Visual header On decorative visuals only
7. One last checkFile ← Options ← Report settings ← make sure thatPersistent filters on

Two pages + a Drill-through page + a Tooltip page, a synced slicer and working navigation. Every number on the screen comes from one of the 14 measures.

1. tableTable ← Region_Name, [Total Billed Revenue], [Region Tier]
2. technique AFormat ← Cell elements ← Background color ← Format style = Rules
3. rules>= 250000 ← #132449 · >= 150000 ← #F5C518 · otherwise #A9B4CC
4. technique BThe same screen ← Format style = Field value ← [Tier Color]
5. the checkThe two techniques must give exactly the same picture — 6 dark, 3 yellow, 3 gray
6. the differenceRules lives inside the visual; the measure lives in the model and serves every visual in the file

When each one: Rules Fast for a single visual and doesn't require a measure. Field value It's the only thing that guarantees every visual in the report tells the same color story - and when someone changes the threshold from 250,000 to 260,000, it's one line in the measure instead of editing every visual separately.

The two techniques: 6 cells #132449 · 3 #F5C518 · 3 #A9B4CC.

The answer: the average of ratios is not the ratio of the sums. Revenue_Per_GB calculated for each row separately — Billed_Amount_USD / Data_Volume_GB — and therefore a row that consumed 0.03 GBP and paid 6.00 USD contributes a ratio of 200 Just like a row that consumed 400 etc. AVERAGE gives both of them equal weight, and the small rows — which are the majority — pull the result up.

What to do instead: Calculate the ratio once at the aggregate level, don't average ratios:

True Revenue per GB =
DIVIDE (
    [Total Billed Revenue],
    SUM ( Fact_Usage[Data_Volume_GB] )
)

and why the column shouldn't be summed at all: SUM(Revenue_Per_GB) returns 1,374,449.63 — a number that has no unit and no meaning. Any column that has already been divided isn't additive, so it needs to be hidden in the model (Hide in report view) so no one drags it by mistake.

The correct answer: 1.2057 USD to GBP (2,594,253.85 ÷ 2,151,653.92 etc.).

Margin From Loss Rows =
CALCULATE (
    [Total Gross Margin],
    Fact_Usage[Gross_Margin_USD] < 0
)

Loss Rows Revenue =
CALCULATE (
    [Total Billed Revenue],
    Fact_Usage[Gross_Margin_USD] < 0
)

What the numbers say: 2,824 Rows out of 30,000 (9.41%) carry a negative margin. They generate 241,146.59 USD revenue — and delete 50,484.13 USD margin. Without them the margin would be 940,189.78 instead 889,705.65.

Why this matters specifically here: 9.41% From the rows it looks negligible, but they are concentrated in one service — Device Instalment — which is entirely below the break-even point. Summing across the whole file completely hides this; only breaking it down by service reveals it. That is exactly why exercise 2.9 is built as a Waterfall and not as bars.

the offset: 940,189.78 minus 50,484.13 equals 889,705.65 USD.

1. tableTable ← Channel_Name, [Total Billed Revenue], [Total Gross Margin]
2. Margin lessonhelper measure: DIVIDE ( [Total Gross Margin], [Total Billed Revenue] ) In percent format
3. First sortsorted by revenue descending — and note who's on top
4. second sortby interval descending — and note who's at the top now
5. comparisonAdd the [Top Margin Channel Rev] From exercise 1.13 as a reference column

What we see: Authorised Dealer Leads in revenue with 518,340.19 USD — but its margin rate is only 26.42%, and the actual spacing 136,924.32 USD. In comparison, Web Self Service Generates less revenue (409,216.29) but More spacing — 203,233.89 USD at a rate 49.66%.

What this means: The gap isn't in the price the customer pays — it's in the cost of acquisition. Direct digital channels don't pay a commission, and partner channels do. Therefore Field Sales Agency ends with 15.24% only. Ranking channels by revenue is a ranking of turnover, not of contribution.

What's recommended: Don't close the Partners channel — it's still generating 280,856.35 USD positive margin. The recommendation is to move existing customers to digital renewal and keep the partners for new acquisition only. And every channel target metric should be on margin, not on revenue.

Leads in revenue Authorised Dealer; the leader by a margin Web Self Service. Margin percentage gap: 49.66% against 15.24%.

All results were verified in pandas against Telecom_PowerBI_Star.xlsx (30,000 fact rows), and all seven relationships were checked programmatically — 0 orphan keys, 0 blank keys, 0 circular dependencies and 0 duplicate filter paths.