Track To Success: Deciphering UK Railways Trend

▶ Objective : For the Maven Rail Challenge, you'll play the role of a BI Developer for National Rail, a company that provides business services to passenger train operators in England, Scotland, and Wales.

▶ Role: You've been asked by your manager to create an exploratory dashboard that helps them:

▶ Business Needs:

• Identify the most popular routes.
• Determine peak travel times.
• Analyze revenue from different ticket types & classes.
• Diagnose on-time performance and contributing factors.

▶ Approach:

1. Route Popularity:

• Considered Measure:- # Tickets Bought
• I've used a Custom Visual called "Flow Map" to visualize most popular routes and using #Tickets_Bought as line width.

2. Analyzing Peak Travel Times:

• Considered Measures:- Departure Hour & Arrival Hour
• Used Matrix Visual as Heat-Map to highlight busiest Departure / Arrival Hours.

3. Analyzing Revenue:

• Key Insight: Popularity <> Most revenue generated ( Revenue for Most popular route = 17,310 pounds whereas 3rd and 7th most popular routes are generating 183,193 pounds and 113,299 pounds respectively)
• Utilized Donut Charts to show %Share of dimensions like Purchase Type, Railcard Type, Tickets Type and Ticket Class.
• Revenue Trend has a clear impact by Rail Strikes In 2024 at highlighted 3 dates. (30th Jan, 1st Feb and 29th Feb)

4. Analyzing Performance:

• Considered Measure: Avg. Delay Time.

    AVERAGEX("UK Railway", 

        DATEDIFF('UK Railway'[Arrival Time], 'UK Railway'[Actual Arrival Time],MINUTE)).

• Used Matrix Visual on How Does Avg. Delay Time (Min) affects most busiest routes. (London St Pancras to Birmingham New Street is 5th most popular route with 0.0 Min of Avg. Delay Time)
• Same Matrix but for Least Busiest Routes. (Manchester Piccadilly to Leeds Seems the most delayed arrival station with Avg. Delay Time of 65.24 Minutes)

5. Most Frequent Delay Reasons and Avg. Delay Time Analysis.

• Weather (995),
• Signal Failure (970) and
• Technical Issue (707) are Top 3 Reasons for delayed journeys.
• But the delay reason by Avg. Delay Time tells different story, Staff Shortage (74.93 Min) of Avg. Delay Time inspite being 4th at most frequent delay reasons followed by Signal failure (51.81 min) and Weather (46.81 mins).