Maven Rail Challenge

UK National Rail Project Description

1. Introduction

National Rail, UK, runs the rail service for its customersa. Train ticket data for National Rail in the UK was provided from Jan to April 2024, including details on the type of ticket, the date & time for each journey, the departure & arrival stations, the ticket price, and more. The main goal of this project is to create an interactive dashboard using PowerBI for managers to track rail performance.

2 Objectives

The main objective is to create an exploratory dashboard that helps them:

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

1. A comparative analysis to identify the most popular routes in different stations.

2. Present a summary to determine peak travel times

3. An analysis of the company's revenue from different ticket types & classes

4. Diagnose on-time performance and contributing factors

With aAnalyze focus on these specific objectives, the dashboard will be designed and analysed to complete the project.

3 Data Preparation & Quality Issues:

A data table was uploaded to the power query editor. The data was clean enough to proceed. However, I have checked the data types of each column. The column profile, quality, and distribution were checked to ensure the quality of the data. Data modelling was not done, as we have only one table to analyse. The data set was 100% valid with no errors.

4 Data Analysis:

A substantial amount of time was invested to understand the nature of the data. Understanding the context of the data is the most important part of the projects. Mostly, I used some bar chart, table, and matrix to define the whole context of the problem.

In any project, the fact tables are the main point of the whole analysis. Consequently, to my knowledge, I started to develop data measures and a calculated column. Using the power query editor, the start of quarter, start of month, and start of week columns were created based on the departure date column to track the time-sensitive distribution of ticket price and revenue. I have also created a calculated column by combining railway[Departure Station] & railway[Arrival Destination] & railway[Date of Journey] & railway[Departure Time] column to calculate the number of trip in the data set.

In this project, the performance of the rail company depended on the popularity of the routes and the earned revenue. More specifically, I prepared measures for total revenue, actual revenue, refund amount, and weekend revenue. total number of trips was calculated using DISTINCTCOUNT function and based on that, I produced on-time trips, delayed trips and cancelled trips using DAX measures. The day-over-day (DoD) change % for revenue and trips was calculated using the time intelligent function. Finally, the average ticket price was calculated using the filter function.

After developing each measure, it is essential to check the value of the measure using a card to justify the expression and also to check for errors.

5 Dashboard development

The dashboard was developed in 4 pages, namely ROUTE, TRAVEL TIME, REVENUE, AND PERFORMANCE. To make a colourful and attractive background, I imported background images from FREEPIK (https://www.freepik.com/free-photos-vectors/dashboard-background). I also made a layout using paper and pen, considering the most vital data and graphs.

Route dashboard

The first page under Popular Route describes the overall performance of different stations across the UK in terms of their on-time, delayed, and cancelled trip over time. Key performance indicators (KPIs) were included at the top of the dashboard to understand the UK performance of the national rail, system. I created a new powerBI-developed ‘text card’ based on the measures developed for all KPIs. A filter was added to the visuals of the segment for departure stations to filter out all stations. All the charts and visuals were formatted based on my selected colour (yellow, light green, purple). I used three colours that refer to the dashboard to make it visually appealing. Three bar charts were added to show the stations with the highest delays, cancelations, and time percentages to define station performance. The table summarises the total trips from and to the departure stations, with a delayed and cancelled number as well.

Travel time

To understand the travel-time-related factors, I produce a heat map to observe the distribution of the greatest number of trips over the day. Line and clustered column charts describe the stations that produce the highest revenue while contributing the most trips. For example, London Kings Cross earned highest revenue among all stations by producing total 2625 trips. The bar chart shows that the stations were usually busy between 6:15 am to 8:00 AM and 3:45 a.m. to 6:45 a.m. On the other hand, the line chart described that most of the train were delayed at the peak hour of 8: 00 AM, with a total of 167 times.

Revenue

In this dashboard, KPIs were included to describe the total revenue, refund amount, actual, weekday, and weekend revenue. A slicer added for departure stations helps to filter out stations to understand at different granular level data. Four donut charts describe the contribution of revenue by payment methods, ticket class, rail card, and ticket type. The clustered column charts were added to describe average ticket price of advance, anytime, and off-pick cost by purchase type and ticket class. For example, first-class anytime ticket price was highest among all tickets.

Performance

The performance dashboard describes the reasons that contribute more to delayed or cancelled trip. ‘Technical issue’ was the highest reason for disruption of the trips. Additionally, revenue was cut mainly for this “technical issue”. The decomposition tree analyses the root cause of the delayed or cancelled trips. Finally, some slicers were added to analyse the data in a granular level.