About this project
This interactive Power BI report consolidates key metrics—from bookings to revenue per available room—to help hotel managers make data-driven decisions. The underlying model is built from anonymised booking, revenue and customer satisfaction data.
Explore the screenshots below or download the PBIX file to interact with the report yourself.
Real-World Problem & Analytic Solution
Problem: Independent hotels often struggle to maximise revenue while minimising cancellations across many booking channels.
Analytic Solution: This dashboard pin-points leakage in ADR, lead-time and channel mix, letting managers swiftly re-price inventory and target valuable guest segments.
🧮 Understanding the Metrics
The dashboard tracks key hotel KPIs. Use this cheat-sheet as you explore the report:
| Metric | Definition & Purpose | DAX Formula |
|---|---|---|
| Total Bookings | # reservations (live + cancelled); baseline volume driver | COUNTROWS( hotel_booking ) |
| Bookings Cancelled | # reservations flagged is_canceled = 1; gauges churn | CALCULATE( [Total Bookings], hotel_booking[is_canceled] = 1 ) |
| Booking Cancellation % | Share of bookings lost; indicator of revenue leakage | DIVIDE( [Bookings Cancelled], [Total Bookings] ) |
| Average Daily Rate (ADR) | Avg price / night booked; pricing power proxy | AVERAGE( hotel_booking[adr] ) |
| Average Lead Time (days) | Avg days between booking & arrival; demand planning metric | AVERAGE( hotel_booking[lead_time] ) |
| Nights | Row-level length of stay (weekend + weekday) | hotel_booking[stays_in_weekend_nights] + hotel_booking[stays_in_week_nights] (column) |
| Revenue Per Booking | Potential revenue per reservation (ADR × Nights) | SUMX( hotel_booking, hotel_booking[adr] * [Nights] ) |
| Total Revenue | Realised revenue (non-cancelled) | CALCULATE( [Revenue Per Booking], hotel_booking[is_canceled] = 0 ) |
| Revenue Loss | Lost revenue due to cancellations | CALCULATE( [Revenue Per Booking], hotel_booking[is_canceled] = 1 ) |
| YoY % Metrics | Year-on-year growth signals (Bookings, Revenue, ADR …) | DIVIDE( [Metric] - [Metric PY], [Metric PY] ) |
Explore the Live Dashboard
Trouble viewing? Open the report in a new tab.
Key Highlights
- Monitor booking trends, ADR and cancellation rates at a glance.
- Slice by hotel type, market segment, channel, room class and more.
- Identify high-value segments and minimise revenue leakage.
Walk-through
- Overview page: spot KPI outliers, YoY trends & cancellations at a glance.
- Analysis page: dig into segments via metric selector, channel mix and country table.
- Filter with slicers (Hotel Type, Year, Segment, Channel) and reset anytime.
📊 Visual Catalogue & Rationale
| Page • Visual | Chart Type | Why this chart? | Key Insight |
|---|---|---|---|
| Overview • KPI strip | Card ×6 | Instant pulse on key numbers + YoY arrows | Bookings ↑51 %, but Revenue Loss ↑7 % → margin risk |
| Overview • Monthly Booking Trend | Combo (column + line) | Trend + targets in one; shows seasonality | Peak Aug–Sep, off-season Dec–Jan → promo window |
| Overview • Cancellation Rate | Donut KPI | Gauge at-a-glance vs 0–100 % | 37 % cancel rate is red-zone |
| Overview • ADR by Country | Bubble scatter | Volume (x) vs Price (y) vs Revenue (size) | Portugal high price & volume; outlier market |
| Overview • ADR by Lead Time | Bar | Easy ranking buckets | Longer lead = higher ADR; price advance bookings higher |
| Overview • Rev vs Rev Loss by Month | Stacked column | Compare good vs lost dollars monthly | Sep top loss despite high revenue |
| Overview • Top Markets Table | Table + data bars | Flags + dual metrics side-by-side | Top 5 countries bring 70 % revenue |
| Overview • Bookings Cancelled by Weekday | Column | Day-of-week pattern | Sunday spikes ⇒ revise weekend policy |
| Overview • ADR & Cancellation % | Line + column | Relate price to cancel behaviour | ADR ↑ above $140 drives cancellations ↑ |
| Analysis • Metric Selector | Button slicer | User chooses KPI | Tailors every chart to GM’s question |
| Analysis • Revenue by Customer Type | Donut | Proportions fit 4 categories | Transient 74 % revenue; upsell others |
| Analysis • Rev by Channel | Donut | Quick share, small categories | GDS small volume but 16 % revenue (ADR) |
| Analysis • Rev by Nights Spent | Histogram | LOS distribution | Short stays <4 nights dominate revenue loss |
| Analysis • Rev by Market Segment | Horizontal bar | Ranked list | Online TA twice offline; direct minor |
| Analysis • Top 15 Countries | Flag table | Rich tabular with icons | Portugal, UK, FR = 60 % of total |
| Analysis • Rev by Room Type | Tornado | Visualises upgrade leakage | Type A over-assigned: 55 % free upgrades |
Screenshots
Download the Report
Download PBIX (11 MB)Source code available on GitHub.
Insights & Recommendations
| # | Insight | Recommendation |
|---|---|---|
| 1 | Portugal tops revenue: $11 M, ADR $140. | Increase paid-search budget & launch “remote-work” package. |
| 2 | Resort Hotel cancellations 37 %. | Tighten deposit terms; 5 % discount for non-refundable 14-day bookings. |
| 3 | GDS channel ADR $156 but 8 % share. | Negotiate rate-parity; add loyalty perks for GDS. |
Tech Stack
- Power BI Desktop (Jul 2025) – data model, DAX & visuals
- Power Query – data cleansing & feature engineering
- GitHub Pages – static site hosting for this project page
- FlagCDN – ISO-country flag icons
Contact
Created by Carlton Njong. Reach out: