Pharma Industry Summary Assignment — Star Schema in Power BI

Capstone assignment · Pharma Industry

Pharmaceutical industry model — preparation and assignment structure

Two dashboard pages where every number comes from a measure you write from scratch, with automatic color indication for product tiers. Working on Pharma_PowerBI_Star.xlsx — a pure star schema: one fact table, 28,400 rows, seven dimensions and only seven relationships. 29 exercises, each on a separate page with a full solution. Note: the download and the practice are On the computer only — Power BI Desktop doesn't exist on phone.

Dataset download — Pharma_PowerBI_Star.xlsx 8 tables · 28,400 rows · 7 relationships · For download on computer only

98,172,830.75USD net revenue — the anchor number

28,400Rows in the fact table

7Dimensions · 7 relationships

14Measures to write

The data is synthetic. The drug names, manufacturers, pharmacies and doctors were generated artificially. Don't draw any conclusions from them about the real pharmaceutical industry — but the economic structure is realistic: brand-name drugs carry a high gross margin, and generics a thin one.

Before we start — building the model. Load the eight sheets, turn off the Autodetect and manually build the seven relationships by sheet Relationships: Every dimension connects directly to Fact_Sales, all of them One to Many in the direction Single from the dimension to the fact. There is not and will not be a dimension-to-dimension relationship. Mark the Dim_Date As a date table (Mark as date table on Full_Date).

The trap of this bundle — cancelled rows that carry unrealized revenue. in the column Fact_Sales[Order_Status] Four values: Dispensed, Refilled, Cancelled andReversed. The last two are rows that were canceled or returned — yet they still carry a full amount inNet_Revenue_USD, because that's how the operational system records them. 2,514 Such rows, that carry 8,834,061.88 USD that never made it into the register — 9.0% from the naive sum. A measure that sums the column without a status filter will show revenue that doesn't exist. Throughout the assignment we'll write both numbers explicitly to see the gap.

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

Assignment structure

Part 1

14 DAX measures

Two SUMs, two DISTINCTCOUNTs, DIVIDE, two CALCULATEs, ALL, IF, two SWITCHes — and three window functions: RANK, INDEX and WINDOW. Plus one more exercise where you don't write a measure but explain why a naive number is misleading.

Part 2

Two dashboard pages

A KPI tray, images, four cards, a bar chart, a smoothed line chart, a slicer on the second page, a detail table, Top N, Matrix, a combo chart, and polished navigation.

Part 3

Conditional formatting and insight

The same three-tier split using two techniques — Rules inside the visual, and a SWITCH measure that feeds color to each visual — and finally a channel-profitability question.

All the numbers were verified in pandas against the file

1.1 · Total Net Revenue

Create a measure named Total Net Revenue that summarizes the Fact_Sales[Net_Revenue_USD]. Format with a thousands separator and two decimal digits.

Total Net Revenue =
SUM ( Fact_Sales[Net_Revenue_USD] )

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

Why specifically Net and not Gross: the column Gross_Revenue_USD It's the list price times the quantity, before discounts. Net_Revenue_USD It's the same column minus Discount_USD — meaning what was actually charged. The gap between the two in this file is 7,465,587.96 USD.

result: 98,172,830.75 USD. This is the naive number — we'll return to it in exercise 1.7.

1.2 · Total Units Dispensed

Create a second measure named Total Units Dispensed that summarizes the Fact_Sales[Units_Dispensed]. Note that this is an entirely different column from exercise 1.1.

Total Units Dispensed =
SUM ( Fact_Sales[Units_Dispensed] )

Two columns, two worlds: Net_Revenue_USD it's money andUnits_Dispensed It's a physical quantity. Both are fully additive — breaking either of them down by any of the seven dimensions sums up exactly to the total. Later we'll discover that the product with the most units isn't the product with the most money, and that's exactly the point.

result: 1,096,897 units.

1.3 · Active Products

Create a measure named Active Products that counts products unique based on Fact_Sales[Product_ID].

Active Products =
DISTINCTCOUNT ( Fact_Sales[Product_ID] )

Why not COUNT: COUNT ( Fact_Sales[Product_ID] ) would return 28,400 — it counts rows that have a value, not unique values. Every product appears here, on average, in236.7 Sales rows.

result: 120 products — exactly all the rows in Dim_Product, meaning there's no product in the catalog that never sold.

1.4 · Prescribers Engaged

Create a second DISTINCTCOUNT measure named Prescribers Engaged — this time on Fact_Sales[Prescriber_ID]. A different column, a different question.

Prescribers Engaged =
DISTINCTCOUNT (
    Fact_Sales[Prescriber_ID]
)

What question does the measure answer: 1.3 asks "how many products moved", and 1.4 asks "how many doctors prescribed at all". Two distinct numbers, two different columns in the same fact table. Note that this measure changes with every slice: with a slicer on a single region you'll get a much smaller number, and the sum of the numbers across all regions no will be 300 — a distinct count is not additive.

result: 300 Doctors prescribe.

1.5 · Revenue per Unit

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

Revenue per Unit =
DIVIDE (
    [Total Net Revenue],
    [Total Units Dispensed]
)

The Lego principle: A new measure is built from existing measures, not columns. That way a change to the revenue definition automatically rolls through to everything that depends on it. DIVIDE returns blank instead of an error when the denominator is zero — / would crash the visual.

Interpretation warning: This is a price per unit weighted, not an average of prices. Cheap generics take up 38.2% From the units but only 2.8% of the money, so they pull this number down.

result: 89.5 USD per unit.

1.6 · Oncology Revenue

Create a measure named Oncology Revenue that returns the net revenue of the oncology department only, even when the visual is sliced by something completely different.

Oncology Revenue =
CALCULATE (
    [Total Net Revenue],
    Dim_Product[Therapeutic_Area]
        = "Oncology"
)

What CALCULATE does here: It replaces the filter on Therapeutic_Area in its own filter. If the visual is already filtered to Cardiology — the measure will still return Oncology. This is the normal behavior of CALCULATE, and in the next exercise we'll see how to change it.

result: 47,694,982.99 USD — 48.58% of total revenue. Oncology is 18% roughly of the products but almost half the money.

1.7 · Realized Revenue

Create a measure named Realized Revenue that returns only revenue that was actually realized — meaning rows whose status Dispensed or Refilled. Use -KEEPFILTERS so that the measure will honor the status slicer the user chose, and won't override it.

Realized Revenue =
CALCULATE (
    [Total Net Revenue],
    KEEPFILTERS (
        Fact_Sales[Order_Status] IN
            { "Dispensed", "Refilled" }
    )
)

Why KEEPFILTERS and not regular CALCULATE: without KEEPFILTERS, if the user selects in the slicer Cancelled only — the measure will override the selection and still return the sum of Dispensed andRefilled. The user will see a large number under the "Canceled" heading and won't understand. KEEPFILTERS Intersects the two filters with each other instead of replacing: choosing Cancelled will give blank, and that's the correct answer.

The gap versus 1.1: 98,172,830.75 less 89,338,768.87 they 8,834,061.88 USD of unrealized revenue — 9.0% than the naive number.

result: 89,338,768.87 USD realized, out of 98,172,830.75 naive.

1.8 · Pct of All Products

Create a measure named Pct of All Products that returns, for each product, its percentage share of the total revenue of all the rows inDim_Product — meaning a denominator that doesn't move as the row changes. Format as a percentage.

Pct of All Products =
DIVIDE (
    [Total Net Revenue],
    CALCULATE (
        [Total Net Revenue],
        ALL ( Dim_Product )
    )
)

What ALL does: It removes all filters from the dimension Dim_Product — not just from the name column, but also from the treatment field, the shape, and-Is_Generic. The denominator becomes constant, while the numerator keeps changing row by row.

The sanity test: in the table by Product_Name The Total row must show exactly 100.00%. If it shows less — you forgot ALL somewhere.

Result: the leader Endomycin = 7.36% · Total row = 100.00%.

1.9 · Revenue vs Target

Create a text measure named Revenue vs Target That compares each product to a target constant of 750,000 USD and returns Above Target or Below Target.

Revenue vs Target =
IF (
    [Total Net Revenue] >= 750000,
    "Above Target",
    "Below Target"
)

A fixed target vs. a calculated target: Here the target is a number hard-coded in the code. That’s intentional — a business target is set in advance and doesn’t move when the data moves. For comparison, the average revenue per product in the file is 818,106.92 USD, close to the target but not identical to it.

result: 35 Products above target · 85 beneath it.

1.10 · Product Tier — SWITCH

Create a measure named Product Tier that splits each product into three tiers by net revenue: Top from1,500,000 and above, Mid from400,000 and above, and everything else Low. Use -SWITCH ( TRUE () ), not in a nested IF.

Product Tier =
VAR R = [Total Net Revenue]
RETURN
SWITCH (
    TRUE (),
    R >= 1500000, "Top",
    R >= 400000, "Mid",
    "Low"
)

why VAR: without the variable, [Total Net Revenue] It was calculated twice in every row. With VAR it's calculated once, and the code also reads better.

The order is critical: SWITCH stops at the first condition that's met. If you swap the two rows, a product of 2,000,000 will get Mid — because it too is greater than400,000, and this condition is checked first.

result: 22 Top · 31 Mid · 67 Low — total 120 products.

1.11 · Tier Color — SWITCH on a value

Create a measure named Tier Color that translates every layer into a color code. This is SWITCH of the second kind — not TRUE () but a direct match to a value.

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

Two faces of SWITCH: In 1.10 the first argument was TRUE () and every pair is condition and result. Here the first argument is an expression, and each pair is value and result. The same function, two usage forms.

note: This measure is never shown to the user. It exists to feed conditional formatting — we'll use it in exercise 3.2.

The three values: #132449 · #F5C518 · #A9B4CC.

1.12 · Product Rank — dense RANK

Create a measure named Product Rank that ranks products by net revenue from highest to lowest, in a ranking crowded (DENSE) — meaning without skipping numbers after a tie.

Product Rank =
RANK (
    DENSE,
    ALLSELECTED (
        Dim_Product[Product_Name]
    ),
    ORDERBY (
        [Total Net Revenue], DESC
    )
)

DENSE vs SKIP: If two products share place 3, DENSE would give the one after them 4, andSKIP will give it 5. In the ranking shown to the user DENSE Almost always what you want.

Why ALLSELECTED and not ALL: ALLSELECTED respects slicers. If the user filtered to Oncology, the ranking is rebuilt within that group and starts from 1 — which is what a user expects to see. With ALL The ranking would have stayed global and the table would have started at place 7 or 12.

result: Endomycin = 1 · Branpril = 120 (the last place among 120).

1.13 · Leader Revenue — INDEX

Create a measure named Leader Revenue that returns, in each row, the revenue of the product the leader — the exact same number in every row of the table.

Leader Revenue =
CALCULATE (
    [Total Net Revenue],
    INDEX (
        1,
        ALLSELECTED (
            Dim_Product[Product_Name]
        ),
        ORDERBY (
            [Total Net Revenue], DESC
        )
    )
)

What INDEX returns: One row out of a sorted list. 1 = the first, -1 = the last one. Here the sort is descending by revenue, so the first row is the top one.

Why this is useful: Once you have the leader's value in every row, you can write the percentage gap from the leader in a single line: DIVIDE ( [Total Net Revenue], [Leader Revenue] ).

result: 7,230,122.08 USD in every row — the revenue of Endomycin.

1.14 · Rolling 3M — WINDOW

Create a measure named Rolling 3M Revenue — A three-month moving average: the current month and the two months before it.

Rolling 3M Revenue =
AVERAGEX (
    WINDOW (
        -2, REL, 0, REL,
        ALLSELECTED (
            Dim_Date[Month_Key],
            Dim_Date[Month_Year]
        ),
        ORDERBY (
            Dim_Date[Month_Key], ASC
        )
    ),
    [Total Net Revenue]
)

To read the arguments: -2, REL = two rows back relative to the current row · 0, REL = up to the current row itself. Together: a window of three rows. AVERAGEX Runs the measure on each one and returns an average.

Why sort by Month_Key and not by Month_Year: Month_Year it's text — Apr-2025 import before Jan-2026 in the alphabet and will ruin the window. Month_Key It's an integer in the format YYYYMM, so its sort order is chronological. Both columns must be in the table the window is built on.

The first two rows: They don't have three months back, so they return the average of one and two rows respectively. This is correct behavior, not a bug.

result: Mar-2024 = 3,870,749.35 · Jun-2026 = 2,876,205.93 — over 30 months.

1.15 · Why the naive number is misleading

In this exercise, you don't write a measure. The CFO looks at the card you built in 1.1 and sees 98,172,830.75 USD. He asks you: "Is this the money that came in over the last two and a half years?" Explain — in your own words, before you open the solution — why the answer "yes" is wrong, why the mistake isn't caught by any consistency check, and what you would tell him instead.

the answer. the number 98,172,830.75 summarized the Net_Revenue_USD on all 28,400 The rows — including 1,730 rows Cancelled and 784 rows Reversed. Such rows were recorded in the operational system with the full amount, because the amount is set at order time, not at delivery time. They carry 8,834,061.88 USD that never made it into the register.

Why it doesn't look wrong in the visual. The column is fully additive: every breakdown of it — by channel, by region, by month — sums up to exactly the same total. No consistency check will flag it. The error isn't arithmetic, it's definitional: the column answers the question "how much money was recorded", and the user read it as though it answers "how much money came in".

What each of the two numbers actually says
the measure	What it counts	the result
Total Net Revenue	Every row that was recorded, including cancelled and returned	98,172,830.75
Realized Revenue	Only Dispensed and Refilled	89,338,768.87
the gap	Recorded revenue that is unrealized	8,834,061.88 (9.0%)

The gap isn't uniform, and that's the dangerous part. If the cancellations were spread evenly, the naive number would be inflated by the same 9.0% everywhere and the rankings would remain correct. In practice they're not evenly distributed: the leader Endomycin descending from7,230,122.08 to 6,671,042.61 When filtering to the fulfilled status, whereas Renanavir descending from6,893,688.31 to6,261,554.95 — a smaller drop in percentage. Every ranking, every percentage, and every "who is leading" is rebuilt from scratch.

What actually happens. Three options, in order of preference: (1) measure Realized Revenue with KEEPFILTERS As we wrote in 1.7, and we also leave the naive number in the dashboard under an explicit name like Booked Revenue; (2) a report-level filter on Order_Status — simple, but hides the information about cancellations entirely; (3) filtering in Power Query — faster, but the rows are deleted from the model and you can no longer analyze the cancellations themselves. Don't choose (3) before making sure no one needs to count cancellations.

the gap: 8,834,061.88 USD — 9.0% than the naive number.

2.1 · Background, title, and KPI tray

Build the skeleton of the first dashboard page: a solid dark background, a top title bar, and a transparent tray to hold the four cards from exercise 2.3.

Page background
  Color = #0C1114
  Transparency = 0%

Shape: Rectangle (title bar)
  Position X 0  Y 0
  Width 1280  Height 76
  Fill #132449

Shape: Rectangle (KPI tray)
  Position X 24  Y 96
  Width 1232  Height 150
  Fill #FFFFFF  Transparency 92%
  Rounded corners 12
  Border on, color #FFFFFF 88%

Why a tray and not four backgrounds: Four cards on one background read as a group. Four separate rectangles read as four unrelated things. That's the only difference, and it's a big one.

Layer order: inFormat ← Selection pane Make sure the tray is below to the cards. If it's above them they'll disappear and the frustration will be huge.

A canvas sized 1280x720, a title bar 76 tall, and one tray 150 tall — empty for now.

2.2 · Images and logo

Add a small logo and a topic icon to the header bar, and set them so they will not stretch or blur when the page size changes.

Insert ribbon -> Image
  Scaling = Fit  (never Fill)
  Lock aspect ratio = On
  Position X 24  Y 18
  Height 40

Title text box
  Font Segoe UI Semibold 22
  Color #F5C518
  Vertical align = Middle

Fit vs Fill: Fill Fills the frame and crops whatever overflows — a square logo in a wide frame will lose its sides. Fit Keeps the proportions and adds margins. For a logo, always Fit.

accessibility: For every image that isn't decorative, add General ← Alt text. A screen reader will read it aloud.

Logo and icon at height 40 in the header bar, original ratio preserved, both with Alt text.

2.3 · Four cards

Place four cards on the tray: Total Net Revenue, Realized Revenue, Total Units Dispensed andActive ProductsArrange them at equal width with the same spacing between them.

Card 1  [Total Net Revenue]
  Callout value: 28pt, #FFFFFF
  Display units = Millions
  Value decimal places = 2
  Category label = On

Card 2  [Realized Revenue]
  Callout color #F5C518

Card 3  [Total Units Dispensed]
Card 4  [Active Products]

Align: Format -> Align ->
       Distribute horizontally

Why two revenue cards side by side: That's the whole point of the package. The user sees 98,172,830.75 and next to it 89,338,768.87 and immediately asks why there's a difference — and that's exactly the question we wanted them to ask. A single card would have hidden it.

Display units: inMillions The card will show 98.17M. This is readable, but make sure the user understands the unit — add it to the card title, not just the value.

Four cards: 98,172,830.75 · 89,338,768.87 · 1,096,897 · 120.

2.4 · Bar chart by domain

Build a horizontal bar chart (Clustered bar chart) of Total Net Revenue by Dim_Product[Therapeutic_Area], sorted from highest to lowest.

Clustered bar chart
  Y axis: Therapeutic_Area
  X axis: [Total Net Revenue]
  Sort by X axis, Descending
  Data labels = On
  Display units = Millions
  Y axis title = Off
  Gridlines = Off

Why horizontal and not vertical: The domain names are long (Infectious Disease). In a vertical chart they'd be rotated diagonally or cut off. In a horizontal chart they read straight.

The results the chart is supposed to show
Treatment area	Net revenue	percent
Oncology	47,694,982.99	48.58%
Immunology	27,473,749.41	27.99%
Respiratory	4,891,051.68	4.98%
Endocrinology	4,877,562.16	4.97%
Neurology	4,828,940.57	4.92%
Cardiology	4,398,330.28	4.48%
Infectious Disease	2,271,992.01	2.31%
Dermatology	1,736,221.65	1.77%

Eight columns. The leader: Oncology with 47,694,982.99 (48.58%).

2.5 · Smoothed line chart

Build a line chart of Total Net Revenue by Dim_Date[Month_Year], and added a second line to it — Rolling 3M Revenue from exercise 1.14 — to see the trend through the noise.

Line chart
  X axis: Dim_Date[Month_Year]
  Sort by Dim_Date[Month_Key] ASC
  Y axis: [Total Net Revenue]
         [Rolling 3M Revenue]

Lines -> Total Net Revenue
  Stroke width 2, Smooth = Off

Lines -> Rolling 3M Revenue
  Stroke width 3, Smooth = On
  Color #F5C518

Sorting is everything. If you didn't sort by Month_Key, the axis will be sorted alphabetically and you'll get a meaningless zigzag line. Sort by column on Month_Year in the date table solves this for good.

Divided by what: The raw line is left sharp — it's the data. The moving average line is smoothed — it's the explanation. Smoothing both means hiding the noise that's the reason you added the average in the first place.

30 points. Peak: Jan-2025 with 4,280,304.53 USD. The first two points of the moving average are partial.

2.6 · Second page and slicer

Create a second dashboard page named Detail, and added a slicer on it for Dim_Channel[Channel_Name]. We synced it to the first page so that a selection on one page affects the other — without showing the slicer itself twice.

New page -> rename to Detail

Slicer: Dim_Channel[Channel_Name]
  Style = Tile
  Selection = Multi-select
  Show Select all = On

View ribbon -> Sync slicers
  Page Overview
    Sync = On   Visible = On
  Page Detail
    Sync = On   Visible = Off

Sync vs. Visible: Two separate checkboxes. Sync means "the selection rolls over to this page". Visible says "the slicer is drawn on this page". Marking Sync alone is exactly what's wanted here: the filter passes through, the control doesn't take up space twice.

A common trap: If you duplicate a page (Duplicate page) the duplicated slicer no will sync automatically. Check the table inSync slicers After every duplication.

Two pages, only one visible slicer, five channel values, multi-select active.

2.7 · Product detail table

on the page Detail Build a table: Product_Name, Total Net Revenue, Realized Revenue, Pct of All Products, Product Rank andProduct Tier. Add a summary row.

Table visual
  Columns:
    Dim_Product[Product_Name]
    [Total Net Revenue]
    [Realized Revenue]
    [Pct of All Products]
    [Product Rank]
    [Product Tier]

  Totals = On
  Sort by [Total Net Revenue] DESC
  Column headers: wrap text On
  Values font size 11

What the Total row must show: Pct of All Products = 100.00% exactly. Product Rank you'll see either blank or 1 there — that's correct behavior for a window function in the overall context, not a bug. If you want to clear it, wrap the measure in IF ( HASONEVALUE ( ... ), ... ).

The gap the table reveals: The difference between the two revenue columns is not a constant percentage across rows. This is the most direct view of the trap from exercise 1.15.

120 rows. Total = 98,172,830.75 · 89,338,768.87 · 100.00%.

2.8 · Top N

Add a bar chart that shows only the top ten products by revenue. UseTop N in the Filters pane, not in a manual filter of names.

Filters pane -> Product_Name
  Filter type = Top N
  Show items = Top 10
  By value = [Total Net Revenue]
  Apply filter

Visual: Clustered bar chart
  Sort by [Total Net Revenue] DESC
  Data labels On, Millions, 2 dp

Why not just write ten names by hand: The Top N filter is recalculated on every slice. If the user selects in the slicer the Mail Order, the list will update to the top ten In this channel. A fixed list of names would stay stuck on the global leaders and show irrelevant data.

A check worth running: Replace the By value to[Total Units Dispensed] and you'll get an almost completely different list — Oncozumab Leads in quantity with 30,681 units, and it isn't in the money top ten at all.

The top ten by net revenue
place	product	Net revenue
1	Endomycin	7,230,122.08
2	Renanavir	6,893,688.31
3	Xenoparib	5,508,735.27
4	Novapril	3,999,308.12
5	Zyracycline	3,955,566.68
6	Glycomycin	3,709,924.04
7	Dermotinib	3,237,126.65
8	Lumidipine	3,093,283.48
9	Lumizumab	2,937,393.02
10	Endoparib	2,806,066.66

Ten columns. Place 1: Endomycin · Place 10: Endoparib.

2.9 · Matrix — region vs. channel

built Matrix: rows = Dim_Product[Therapeutic_Area], columns = Dim_Channel[Channel_Group], values = Total Net Revenue. Add row and column totals.

Matrix
  Rows:    Therapeutic_Area
  Columns: Channel_Group
  Values:  [Total Net Revenue]

  Row subtotals = On
  Column subtotals = On
  Layout = Tabular
  Stepped layout = Off
  Display units = Millions

The additivity test: The sum of all cells in the body of the matrix must be exactly 98,172,830.75. This works because Total Net Revenue it's pure SUM. Try replacing it withPrescribers Engaged — the summaries no will be summed, because a distinct count isn't additive. The same doctor is counted once in their own row and once in the total.

Only three channel groups: Direct To Patient (Retail Counter + Mail Order), Institutional (Hospital Supply + Specialty Clinic) andTrade (Wholesale Direct). An 8x3 matrix is readable; 8x5 is already too dense for the screen.

8 rows by 3 columns. The large cell: Oncology x Direct To Patient = 26,939,415 USD. Total = 98,172,830.75.

2.10 · Combo chart

built Line and clustered column chart: columns = Total Net Revenue by quarter, a line on the secondary axis = Revenue per Unit.

Line and clustered column chart
  X axis: Dim_Date[Quarter_Name]
  Column y-axis: [Total Net Revenue]
  Line y-axis:   [Revenue per Unit]

  Secondary axis = On
  Line stroke 3, color #F5C518
  Column data labels = On
  Line data labels = Off

When a secondary axis is allowed: Only when the two series measure things different In different units — here dollars versus dollars-per-unit. Two series in the same unit on two axes is misleading: the user sees two lines cross that don't actually cross.

What this chart reveals: Revenue is seasonal — Q1 is strong in both years (11,612,248 and10,858,103), Q3 is weak. But the price per unit barely moved, so the seasonality is quantity and not pricing. This is a conclusion that neither series alone gives.

10 quarters. The highest: 2024-Q1 = 11,612,248.05 USD.

2.11 · Navigation and polish

Finish both pages: navigation buttons between them, consistent titles, precise alignment, and order Tab order Makes sense for keyboard browsing.

Insert -> Buttons -> Navigator
     -> Page navigator
  Position: title bar, X 980 Y 20

Each visual
  Title = On, 12pt, #A9B4CC
  Background = Off
  Border = Off

Format -> Align -> Align left/top
  Distribute vertically

Selection pane -> Tab order
  1 title  2 KPI tray  3 cards
  4 bar  5 line  6 table

Why a Page navigator and not manual buttons: It automatically adds a button for every visible page, marks the current page, and updates itself when a page is added. Manual buttons require Action ← Page navigation for each one, and they break when names change.

The final check before submission: click Ctrl + Shift + F, go through every visual with-Tab alone and make sure the order makes sense. A decorative visual — a rectangle, a divider line — mark Hide inTab order so it will not be caught by the keyboard.

two polished pages, Page navigator on both, Tab order defined, zero overlapping visuals.

3.1 · Conditional Formatting — Rules

Color the Total Net Revenue In table 2.7, in three tiers, using manual rules inside the visual — without using a color measure.

Format visual -> Cell elements
  Series: Total Net Revenue
  Background color = On -> fx
  Format style = Rules
  Based on = [Total Net Revenue]

  If value >= 1500000 and <= MAX
     then #132449
  If value >= 400000 and < 1500000
     then #F5C518
  If value >= MIN and < 400000
     then #A9B4CC

What to pay attention to: The rules are checked in order, so the boundaries must be contiguous and not overlapping. Also note that Power BI distinguishes between is greater than tois greater than or equal to — a product of exactly 400,000 must receive Mid, so >=.

the drawback: The three rules live inside this visual only. Changing a threshold requires going back to each visual separately — and that's exactly what we'll fix in the next exercise.

the division: 22 dark · 31 yellow · 67 gray.

3.2 · Conditional formatting — Field value

Achieve exactly the same result without writing a single rule by hand: the color comes from the measure Tier Color You rewrote it in 1.11.

Format visual -> Cell elements
  Series: Product Tier
  Background color = On -> fx
  Format style = Field value
  What field should we base
  this on? = [Tier Color]

  Font color = On -> fx
  Format style = Field value
  Field = [Tier Text Color]

Why this is stronger: Tier Color it's a single source of truth. One change in the SWITCH code rolls out to every visual that uses it — table, Matrix, chart and card. The rules of 3.1 live in a single visual only.

If the text can't be read On the dark background #132449 — write another short measure and feed it intoFont color The same way:

Tier Text Color =
SWITCH (
    [Product Tier],
    "Top", "#FFFFFF",
    "#111111"
)

Both techniques produce exactly the same split: 22 / 31 / 67.

4.1 · The insight question — channel profitability

An open question to close. The sales manager wants to know which channel is worth investing in. Build a table with the five channels, each one's net revenue, gross margin and margin percentage — and answer: which channel is the most profitable, which is the least, and what explains the gap. Try to answer before you open the solution.

Profitability of the five channels
channel	Net revenue	Gross margin	Margin percent	Revenue share
Retail Counter	39,938,235.90	29,520,127.97	73.91%	40.68%
Hospital Supply	19,966,138.13	14,430,279.62	72.27%	20.34%
Mail Order	15,791,877.94	11,547,821.48	73.13%	16.09%
Specialty Clinic	14,157,189.87	10,688,514.75	75.5%	14.42%
Wholesale Direct	8,319,388.91	5,863,920.93	70.48%	8.47%

1. The largest channel isn't the most profitable. Retail Counter brings 40.68% of revenue — more than any other channel — but its margin percentage 73.91%, lower than that of Specialty Clinic that stands at 75.5%. Every shekel that passes through the specialist clinic generates more profit than a shekel that passes through the counter.

2. The tail is the problem, not the head. Wholesale Direct It is the smallest of the channels (8.47% of revenue) and also the least profitable (70.48%). The product of the two weaknesses is what makes it the first candidate to check — not its size alone and not its percentage alone.

3. Where the gap comes from. The explanation isn't in the channel but in the mix flowing through it. Specialty Clinic carries the expensive drugs with the high margin — oncology and immunology. Wholesale Direct Topic: a large volume of cheap generics, where the cost is 58% until 78% from the price. The channel isn't "less efficient"; it simply sells different products. That's the distinction that separates a correct conclusion from an expensive decision.

4. What does that say about the decision. "Close Wholesale Direct" is the hasty conclusion. The channel generates 5,863,920.93 USD gross margin in practice, and most of its costs are variable, not fixed. The right question is whether you can shift toward it a different product mix — not whether to cut it.

5. How to check this yourself. Build a Matrix with Channel_Name in rows andIs_Generic in columns, and a value DIVIDE ( SUM ( Fact_Sales[Gross_Margin_USD] ), [Total Net Revenue] ). The gap between the columns will be much larger than the gap between the rows — and that's the proof that the mix, not the channel, is what explains the profitability.

And one last warning: All the numbers in this table were calculated on naive revenue. Run them again with Realized Revenue Before you present them to anyone — the order of channels by margin percentage might change, and that's exactly why we wrote that measure.

The most profitable by profit margin percentage: Specialty Clinic (75.5%) · the least profitable: Wholesale Direct (70.48%).

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 Net Revenue	98,172,830.75 USD
Total Units Dispensed	1,096,897
Active Products	120
Prescribers Engaged	300
Revenue per Unit	89.5 USD
Oncology Revenue	47,694,982.99 (48.58%)
Realized Revenue	89,338,768.87 — a gap of 8,834,061.88
Pct of All Products	leading 7.36% · Total 100.00%
Revenue vs Target	35 above · 85 Below (target 750,000)
Product Tier (SWITCH TRUE)	22 Top · 31 Mid · 67 Low
Tier Color (SWITCH on value)	#132449 / #F5C518 / #A9B4CC
Product Rank (RANK)	Endomycin = 1 · Branpril = 120
Leader Revenue (INDEX)	7,230,122.08 in every row
Rolling 3M Revenue (WINDOW)	Mar-2024 = 3,870,749.35 · Jun-2026 = 2,876,205.93
The top product by revenue	Endomycin — 7,230,122.08 USD
The top product by quantity	Oncozumab — 30,681 units (not tenths of currency)
The strongest month	Jan-2025 — 4,280,304.53 USD
The most profitable channel	Specialty Clinic — 75.5% gap
The trap of the package	2,514 Cancelled rows carry 8,834,061.88 USD unrealized

All results were verified in pandas against Pharma_PowerBI_Star.xlsx (28,400 fact rows), and all seven relationships were checked programmatically — 0 orphaned keys, 0 blank cells, 0 circular dependencies and 0 duplicate filter paths.