Project Overview
A 7-page Power BI report built to analyze UK train ticket transactions across passenger volumes, revenue, on-time performance, route intelligence, and what-if scenario planning. Built on a star-schema semantic model — one fact table, five dimension tables, six disconnected and parameter tables, and 70+ custom DAX measures — the report covers everything from executive-level KPIs to live scenario simulation for refund policy and pricing decisions.
Data Model
The semantic model follows a star schema with one central fact table joined to five shared dimension tables:
- Fact_Transactions — one row per ticket transaction. Holds ticket price (GBP), departure and arrival times, actual arrival time (null for cancellations), delay minutes, refund request flag (boolean), and foreign keys to all five dimensions. Computed columns add Route (departure → arrival name), Departure Hour, and Time Band (Morning Peak / Afternoon Peak / Off-Peak), keeping peak-period logic in the data layer rather than in DAX at query time.
- Dim_Date — role-playing calendar dimension used twice. The active relationship joins on Purchase Date; the inactive relationship joins on Journey Date and is activated in DAX via USERELATIONSHIP. Any measure that needs to slice by when the journey occurred must use a dedicated variant that explicitly enables this secondary role.
- Dim_Station — role-playing station dimension used twice. Active on Departure Station; inactive on Arrival Station (also activated via USERELATIONSHIP).
- Dim_Ticket — cross-product of Ticket Class (First / Standard), Ticket Type (Advance / Off-Peak / Anytime), and Railcard type.
- Dim_Payment — unique combinations of Purchase Type (Online / Station) and Payment Method (Credit Card / Debit Card / Contactless).
- Dim_Journey_Status — Journey Status (On Time / Delayed / Cancelled) paired with Reason for Delay (Signal Failure, Technical Issue, Weather, Staffing, or blank for on-time journeys).
Six additional disconnected tables have no physical relationship to the fact table:
- Metric Selector (DATATABLE, 6 rows) — drives the Dynamic KPI button slicer on Executive Overview.
- Time Dimension (DATATABLE, 4 rows) — switches the trend chart X-axis between Month, Day of Week, Time Band, and Departure Hour.
- Time Axis (field parameter) — field parameter backing the time-breakdown slicer; maps labels to Dim_Date and Fact_Transactions columns.
- Ticket Axis (field parameter) — field parameter for switching the ticket breakdown axis between Has Railcard, Railcard Type, Ticket Class, and Ticket Type.
- Delay Threshold Minutes (GENERATESERIES(0, 60, 1)) — What-If slider controlling the refund eligibility threshold.
- Price Adjustment % (GENERATESERIES(-30, 30, 1)) — What-If slider simulating a global fare change.
Dashboard Pages & Key Insights
Page 1 — Cover
- Purpose: A branded landing page with custom left-rail navigation buttons linking to each analytical section. All subsequent pages are hidden from the default tab bar (HiddenInViewMode) and are reached exclusively through these buttons.
Page 2 — Executive Overview
- Purpose: A high-level summary for leadership — a five-metric KPI card, a primary trend chart, and a dynamic metric selector.
- KPI Card: A single multi-measure card always shows Total Journeys, Total Revenue, On-Time Rate %, Total Disruptions, and Refund Exposure simultaneously.
- Dynamic Metric Selector: A button slicer over the disconnected Metric Selector table lets users switch the trend chart Y-axis between six metrics without navigating away. Visual interactions are explicitly set to NoFilter between the slicer and the KPI card, so all five headline figures remain visible regardless of which metric is selected for the chart.
- Insight: The separation of the KPI strip (always-on) from the trend chart (user-driven) means leadership can monitor all headline numbers and still drill into any one metric's time trend from the same page.
Page 3 — Passenger Usage
- Purpose: Journey volume breakdown by ticket class, railcard usage, purchase channel, and time-of-day patterns.
- Visuals: Volume by Ticket Class (First vs Standard), Railcard Usage Rate %, Online vs Station Purchase split, peak vs off-peak journey counts, and departure patterns using the precomputed Time Band and Departure Hour columns.
- Insight: Because peak classification is a computed column rather than a DAX filter, peak-period measures are simple CALCULATE filters on a single text value — reliable and performant at any filter granularity.
Page 4 — Sales & Revenue
- Purpose: Revenue decomposition by ticket class, ticket type, payment method, and purchase channel, plus month-over-month trend.
- Visuals: Revenue by Ticket Class and Type (Advance / Off-Peak / Anytime), average price comparisons across classes and types, online purchase rate, and a MoM revenue trend.
- MoM Logic: Revenue MoM % uses DATEADD(..., -1, MONTH) and returns BLANK() when no prior-period data exists, preventing misleading values at the edge of the date range.
- Insight: Splitting revenue by both class and ticket type together reveals whether First Class revenue comes from premium Anytime fares or discounted Advance purchases — a distinction hidden in simple class-level totals.
Page 5 — Railway Performance
- Purpose: On-time performance deep dive — rates, delay severity, disruption causes, and refund exposure.
- KPI Card: On-Time Rate %, Delay Rate %, Cancel Rate %, Avg Delay Minutes (delayed journeys only), and Refund Exposure.
- Disruption Breakdown: Individual measures for Signal Failure, Technical Issue, Weather, and Staffing disruptions — Weather consolidates two source strings ("Weather" and "Weather Conditions") using an IN {} filter.
- Refund Analysis: Refund Exposure (revenue on all transactions with a refund request) and Delay Refund Rate % (refund rate specifically for delayed journeys) cross-reference the refund flag with journey status.
- Insight: Avg Delay Minutes deliberately excludes Delay Minutes = 0 rows, so it reflects the true average severity of actual delays rather than being diluted by the majority of on-time journeys.
Page 6 — Route Analysis
- Purpose: Journey and revenue totals by departure–arrival route pair, with single-route spotlight KPIs.
- Route Spotlight: Top Route Journeys and Top Route Revenue use TOPN(1, VALUES(Fact_Transactions[Route]), ...) to surface the single busiest and highest-revenue route within the current filter context — two metrics that often point to different routes.
- Arrival Station Filtering: Total Transactions (Arrival Station) and Total Revenue (Arrival Station) activate the inactive Arrival Station relationship via USERELATIONSHIP, enabling filtering by destination independently from departure.
- Insight: The busiest route by volume and the highest-revenue route are computed independently — a short, high-priced route can top the revenue chart while a longer, cheaper commuter route dominates by journey count.
Page 7 — Scenario Simulator
- Purpose: Two real-time what-if tools — one for refund policy analysis, one for revenue impact of fare changes.
- Delay Threshold Slider (0–60 min, default 15): Journeys Above Threshold counts delayed journeys at or beyond the selected threshold; Refund Exposure Above Threshold calculates the revenue at risk from those claims; Refund Policy Saving shows the inverse — how much could be saved by denying refunds for delays below the threshold. All three measures activate the journey date relationship so date slicers work correctly.
- Price Adjustment Slider (−30% to +30%, default 0): Simulated Revenue applies the percentage uniformly to all ticket prices. Revenue Delta and Revenue Delta % show the absolute and relative monetary impact. Formatted with a sign prefix (+£#,##0;-£#,##0;£0) so the direction of change is immediately visible.
- Insight: Both sliders use COALESCE(MIN(...), default) to read the selected value — MIN() reliably captures the lower bound when the slicer passes a range into the filter context, and COALESCE ensures a safe fallback when nothing is selected.
Technical Toolkit (DAX & Modeling)
- 70+ DAX Measures in a single dedicated _Measures table, organized across nine display folders: Volume, Rates, Revenue, Refunds, Routes, Peak Travel, On-Time Performance, Dynamic Selectors, and What-If.
- Role-Playing Dimensions: Both Dim_Date and Dim_Station serve two roles each via inactive relationships. Any measure requiring journey-date or arrival-station context must activate the secondary relationship explicitly using USERELATIONSHIP inside CALCULATE.
- Dynamic KPI Pattern: Dynamic KPI = SWITCH([Selected Metric], "Total Journeys", [...], "Net Revenue", [...], ...) — a single measure on the chart Y-axis replaces six separate visuals. Selected Metric reads the button slicer via SELECTEDVALUE(..., "Total Journeys") with a safe default.
- Route Spotlight: TOPN(1, VALUES(Fact_Transactions[Route]), [measure], DESC) inside CALCULATE isolates the single top-performing route for any metric within the current filter context.
- What-If Parameter Capture: COALESCE(MIN('Delay Threshold Minutes'[Value]), 15) — MIN() handles range-style slicers; COALESCE provides the default when no selection is active.
- MoM Edge Protection: Journeys MoM % checks that both a prior month and a next month exist before returning a value, suppressing misleading changes at both ends of the date range.
- Avg Delay Accuracy: AVERAGEX(FILTER(Fact_Transactions, [Delay Minutes] > 0), [Delay Minutes]) — on-time journeys (Delay Minutes = 0) are explicitly excluded so the average reflects actual delay severity, not a diluted fleet-wide average.
eyJrIjoiNTRlNjQyNmItNTI0NS00YWZmLWI2OGEtMzMyZjI0NjQ2MTJkIiwidCI6ImQxZTRmNjc5LTM5NmQtNDIyMS04MzZhLWI3MTQ2M2JjOGE3YiJ9
