UK Rail Analysis: Where the passengers don't ask for refunds for Delays/Cancellation and Railways doesn't do Surge Pricing

The challenge was about doing an indepth dive about Revenue trends, Routes, Cancellations, Delays, and Route and timing popularity.

The first step was to identify all the key fields necessary to do the analysis which was almost all of them.

The next step was to create measures and place them into separate folders so that it is easy to classify and use them when necessary.

Following which, I had to decide what all do I need to show and what should be the distribution, for example I landed on Executive Page, Ticket Types Deep Dive, Route Density, Stations, Departures & Delays, and finally Overview Cards.

I am a big fan of Field Parameters that I learnt in two of the Maven Analytics courses, and try to incorporate them as much as I can, as I feel it is extraordinary to be able to change the visual based on a single click.

I decided to have 6 options: Month Name, Day of the Week, Date of Journey ; Departure Hour, Departure Time, & Departure Station. Also it was fascinating to show Revenue and Tickets sold graphs in parallel so as to find out if they are diverging i.e. anytime the volume of tickets is high but revenue is low or vice versa. Finally also added a slicer to let the users select the date range for all the data on the executive page.

and ofcourse the top tickets had the executive numbers and results like Total Revenue, Total Tickets, Average Revenue/Ticket, Most Popular Route, Most Popular Departure Station, and Most Popular Arrival Station.

I also added Bookmarks and buttons for easy navigation.

On page Tickets type deep dive, I did the similar analysis but for the 3 different types of tickets (Advance, Anytime, and Off-Peak) and narrated the key insights at the top.

In Route Density and Delays Page I leveraged the perfect Sankey chart to show the routes and also added treemaps to highlight delay or cancellation causes. Followed by Gauge charts on fascinating insights about how few % of people ask for a refund despite delay or cancellation.

Also identified top 3 most delayed arrival stations and routes.

again added 3 slicers for deeper dive: Departure Station, Month, & Weekday

On Page 4: I had Key insights at the top and Decomposition Tree for Tickets and Revenue for Departure Stations.

While a Table for all Routes sharing their Total Tickets, Total Revenue, and Average Rev/Ticket. Tooltip highlighted delay for each route upon hovering.

And below it was a Heatmap to highlight footfall for each hour for each day of the week.

and Finally on Page 5: I had all the key numbers about revenue, tickets, delays, cancellation, revenue lost from delay and cancellation and much more.

Page 6 was the tooltip that I could linked to multiple visuals on previous pages for rich details.

Spent a lot of time, but thoroughly enjoyed it, hope I win and keep up my journey to upskill from my favourite platform.

SOME of the Measures I made (for those just starting in their DAX Journey)

Revenue (Off-Peak Ticket) = CALCULATE([Total Revenue (After Refund)],railway[Ticket Type]="Off-Peak")

Anytime Tickets = CALCULATE([Total Tickets sold (After Refund)], railway[Ticket Type]="Anytime")

Anytime Ticket Revenue % = [Revenue (Anytime Ticket)]/[Total Revenue (After Refund)]

Anytime Ticket % = [Anytime Tickets]/[Total Tickets sold (After Refund)]

Total Tickets Sold = COUNT(railway[Transaction ID])

Total Tickets sold (After Refund) = CALCULATE(COUNT(railway[Transaction ID]),railway[Refund Request]="No")

Total Revenue = SUM(railway[Price])

Total Revenue (After Refund) = CALCULATE(SUM(railway[Price]),railway[Refund Request]="No")

Average Revenue per Ticket = [Total Revenue]/[Total Tickets Sold]

Average Revenue per Ticket (After Refund) = DIVIDE([Total Revenue (After Refund)],[Total Tickets sold (After Refund)])

Standard Tickets = CALCULATE([Total Tickets sold (After Refund)], railway[Ticket Class]="Standard")

Revenue (Standard Ticket) = CALCULATE([Total Revenue (After Refund)],railway[Ticket Class]="Standard")

Revenue (Standard Ticket) % = [Revenue (Standard Ticket)]/[Total Revenue (After Refund)]

Total Delay = CALCULATE(SUM(railway[Delay Duration]), railway[Journey Status]="Delayed")

No. of Departure Stations = DISTINCTCOUNT(railway[Departure Station])

Delay in Minutes = CALCULATE(SUM(railway[Minutes Delayed]), railway[Journey Status]="Delayed")

Delay (in Hours) = AVERAGE(railway[Minutes Delayed])

Average Delay = [Delay in Minutes]/[Count of Delays]

Total Refund = CALCULATE [Total Revenue],railway[Refund Request]="Yes")

Tickets Refunded = CALCULATE([Total Tickets Sold],railway[Refund Request]="Yes")

Refund Ticket % = [Tickets Refunded]/[Total Tickets Sold]

Refund $ % = [Total Refund]/[Total Revenue]

Arrival Count = COUNT(railway[Arrival Destination])

Total Revenue (After Refund) from Delayed = CALCULATE ( [Total Revenue (After Refund)], railway[Journey Status]="Delayed")

Total Revenue (After Refund) from Cancelled/Delayed = CALCULATE( [Total Revenue (After Refund)], railway[Journey Status] IN {"Cancelled","Delayed"})

Revenue Lost from Delayed = CALCULATE(SUM(railway[Price]), railway[Journey Status]="Delayed" && railway[Refund Request]="Yes")

Revenue from Delayed Trips = CALCULATE(SUM(railway[Price]), railway[Journey Status]="Delayed")

Refund # for Delayed Trips = CALCULATE(COUNTA(railway[Journey Status]), railway[Journey Status]="Delayed" && railway[Refund Request] = "Yes" )

Refund # % (Delayed) = [Refund # for Delayed Trips] / [Count of Delays]

Count of Delays/Cancelled = CALCULATE(COUNT(railway[Journey Status]), railway[Journey Status] IN {"Delayed","Cancelled"})

Count of Delays = CALCULATE(COUNT(railway[Journey Status]), railway[Journey Status]="Delayed")

% £ asked for Refund (Delayed) = [Revenue Lost from Delayed] / [Revenue from Delayed Trips]

% £ asked for Refund (Cancelled) = [Revenue Lost from Cancelled] / [Revenue from Cancelled Trips]