Bikeshare Dashboard
When and Why We Ride: Around Town
In this dashboard a fictional bike-share company has provided datasets in which to explore data behind peak riding hours, seasonal trends, and what makes registered riders the key to growth.
Define Objectives & KPIs
Goal: Help the bike shop understand when they make the most money, identify trends over time, and learn to better serve both their casual and registered riders.
Key Questions:
When are riders using the service the most?
Which seasons/hours are most profitable?
Who are our riders (registered vs. casual)?
How do revenue and profit trend over time?
Data Collection
Data Source:
Data:
pre-cleaned and provided by Gaelim
taught by Absent Data
Content of files include:
Ride logs: Timestamps, duration, rider type (casual/registered), etc.
Transaction data: Revenue, cost, profit
Customer info: Registration status
Time & date fields: Day of the week, hour, month, season
SQL Data Preparation
Tasks handled in SQL:
Most of the cleaning in this project was already handled, in this example I have provided a potential use case of how it may have been initially cleaned. However in this instance, Microsoft SQL Server and Management Studio were used to join the the provided csv files into a workable dataset for the dashboard
Import Into Power BI
Tools used:
Power Query Editor in Power BI Desktop:
Import cleaned/aggregated SQL outputs
Create date hierarchies (Year, Month)
Create relationships between tables (e.g., rider data ↔ financials)
Data Modeling in Power BI
Created Date Dimension Table for easier filtering
Created measures for:
Total Revenue (
SUM(revenue))Total Profit (
SUM(revenue - cost))Profit Margin (
DIVIDE(SUM(profit), SUM(revenue)))Registered vs. Casual rider ratio
Visual Design & Layout
Card visualizations for quick view of KPIs (Riders, Profit Margin)
Line and bar combo charts for time series (monthly revenue, riders, profit)
Matrix heatmap for hourly earnings by weekday during main hours of riding
Bar chart for seasonal revenue
Donut chart for rider demographic
Color palette - Contrasts highlight seasonal trends and peak hours.
Interactivity Features
Year slicer (2021 vs. 2022) toggle to drill down on values
Tooltip hover on charts for exact values
Filter integration for flexible insights
Publish & Share
Dashboard was published to Power BI
Final Thoughts
This dashboard is a strong example of data storytelling: raw data is transformed via SQL, refined in Power BI, and presented in a way that supports marketing and operations decisions to make informed decisions and stay agile as new data is added to the matrix over time.