Uber Trip Analysis Dashboard

Project Overview

Dashboard Previews

Dashboard 1: Overview Analysis

Dashboard 2: Time Analysis

Dashboard 3: Details View

Business Requirements

Dashboard 1: Overview Analysis

Dashboard 2: Time Analysis

Dashboard 3: Details Tab

Key Performance Indicators

Features & Visualizations

Overview Analysis Dashboard

Time Analysis Dashboard

Details Dashboard

Enhanced User Experience

Dataset Description

Tools & Technologies

DAX Measures

Core KPI Measures

// Total Bookings
Total Bookings = COUNT('Trip Details'[Trip ID])

// Total Booking Value
Total Booking Value = SUM('Trip Details'[fare_amount]) + SUM('Trip Details'[Surge Fee])

// Average Booking Value
Average Booking Value = DIVIDE([Total Booking Value], [Total Bookings], 0)

// Total Trip Distance
Total Trip Distance = SUM('Trip Details'[trip_distance])

// Average Trip Distance
Average Trip Distance = DIVIDE([Total Trip Distance], [Total Bookings], 0)

// Average Trip Time
Average Trip Time = 
AVERAGEX(
    'Trip Details',
    DATEDIFF('Trip Details'[Pickup Time], 'Trip Details'[Drop Off Time], MINUTE)
)

Time Intelligence Measures

// Bookings by Hour
Bookings by Hour = 
CALCULATE(
    [Total Bookings],
    USERELATIONSHIP('Trip Details'[Pickup Time], 'Time'[DateTime])
)

// Booking Value by Day of Week
Booking Value by Day of Week = 
CALCULATE(
    [Total Booking Value],
    USERELATIONSHIP('Trip Details'[Pickup Time], 'Date'[Date])
)

// Pickup Time in 10-min Intervals
Pickup Time Interval = 
FORMAT(
    TIME(
        HOUR('Trip Details'[Pickup Time]),
        FLOOR(MINUTE('Trip Details'[Pickup Time])/10)*10,
        0
    ),
    "hh:mm"
)

Dynamic Measure Selection

// Selected Measure (for dynamic selection)
Selected Measure = 
SWITCH(
    SELECTEDVALUE('Measure Selector'[Measure]),
    "Total Bookings", [Total Bookings],
    "Total Booking Value", [Total Booking Value],
    "Total Trip Distance", [Total Trip Distance],
    [Total Bookings] // Default value
)

// Dynamic Chart Title
Dynamic Chart Title = 
VAR MeasureName = SELECTEDVALUE('Measure Selector'[Measure])
RETURN
    "Analysis by " & MeasureName

Location Analysis Measures

// Most Frequent Pickup Point
Most Frequent Pickup = 
VAR TopLocation = 
    TOPN(1, VALUES('Locations'[Location Name]), 
        CALCULATE([Total Bookings], 
            USERELATIONSHIP('Trip Details'[PULocationID], 'Locations'[LocationID])), 
        DESC)
RETURN
    SELECTEDVALUE(TopLocation, "None")

// Most Frequent Drop-off Point
Most Frequent Dropoff = 
VAR TopLocation = 
    TOPN(1, VALUES('Locations'[Location Name]), 
        CALCULATE([Total Bookings], 
            USERELATIONSHIP('Trip Details'[DOLocationID], 'Locations'[LocationID])), 
        DESC)
RETURN
    SELECTEDVALUE(TopLocation, "None")

// Farthest Trip Distance
Farthest Trip = 
    MAXX('Trip Details', [trip_distance])

Vehicle & Payment Analysis

// Preferred Vehicle by Location
Preferred Vehicle by Location = 
VAR CurrentLocation = SELECTEDVALUE('Locations'[Location Name])
VAR VehicleCount = 
    SUMMARIZE(
        'Trip Details',
        'Trip Details'[Vehicle],
        "Count", COUNT('Trip Details'[Trip ID])
    )
VAR TopVehicle = 
    TOPN(1, VehicleCount, [Count], DESC)
RETURN
    SELECTEDVALUE(TopVehicle[Vehicle], "None")

// Booking Value by Payment Type
Booking Value by Payment = 
    CALCULATE(
        [Total Booking Value],
        ALLEXCEPT('Trip Details', 'Trip Details'[Payment_type])
    )

Expected Outcomes

Repository Contents

Data Model & Relationships

Trip Details (Fact Table)
├── Date [Many-to-One]
│   └── Calendar table with date hierarchies
├── Time [Many-to-One]
│   └── Time dimension with hour-level granularity
├── Locations [Many-to-One]
│   └── Location dimension with location details
└── Measure Selector [Disconnected]
    └── Used for dynamic measure selection

Key Table Structures

How to Use

Data Preparation & ETL Process

// Power Query M Code for Trip Data Transformation

let
    Source = Excel.Workbook(File.Contents("Uber Trip Details.xlsx"), null, true),
    TripDetails = Source{[Item="Trip Details",Kind="Sheet"]}[Data],
    
    // Promote headers and set data types
    PromotedHeaders = Table.PromoteHeaders(TripDetails, [PromoteAllScalars=true]),
    ChangedTypes = Table.TransformColumnTypes(PromotedHeaders,{
        {"Trip ID", Int64.Type},
        {"Pickup Time", type datetime},
        {"Drop Off Time", type datetime},
        {"passenger_count", Int64.Type},
        {"trip_distance", type number},
        {"PULocationID", Int64.Type},
        {"DOLocationID", Int64.Type},
        {"fare_amount", type number},
        {"Surge Fee", type number},
        {"Vehicle", type text},
        {"Payment_type", type text}
    }),
    
    // Add calculated columns
    AddedTripDuration = Table.AddColumn(ChangedTypes, "Trip Duration (min)", 
        each Duration.TotalMinutes([Drop Off Time] - [Pickup Time]), type number),
    
    // Calculate total fare
    AddedTotalFare = Table.AddColumn(AddedTripDuration, "Total Fare", 
        each [fare_amount] + [Surge Fee], type number),
    
    // Add time-based categorization
    AddedTimePeriod = Table.AddColumn(AddedTotalFare, "Time Period", 
        each if Time.Hour([Pickup Time]) >= 6 and Time.Hour([Pickup Time]) < 12 then "Morning"
            else if Time.Hour([Pickup Time]) >= 12 and Time.Hour([Pickup Time]) < 18 then "Afternoon"
            else if Time.Hour([Pickup Time]) >= 18 and Time.Hour([Pickup Time]) < 22 then "Evening"
            else "Night", type text),
    
    // Add day/night categorization
    AddedDayNight = Table.AddColumn(AddedTimePeriod, "Day or Night", 
        each if Time.Hour([Pickup Time]) >= 6 and Time.Hour([Pickup Time]) < 20 then "Day" else "Night", 
        type text),
    
    // Add day of week name
    AddedDayName = Table.AddColumn(AddedDayNight, "Day Name", 
        each Date.DayOfWeekName([Pickup Time]), type text),
    
    // Remove any null values in critical columns
    RemovedNulls = Table.SelectRows(AddedDayName, 
        each [Trip ID] <> null and [Pickup Time] <> null and [Drop Off Time] <> null)
in
    RemovedNulls

Additional Data Processing Steps:

Sample Insights & Key Findings

Booking Patterns

Vehicle Usage

Payment Analysis

Trip Efficiency

Geographic Insights

Recommendations