🔍 What’s This Article About?
If your Occupancy, Capacity, or RevPAR numbers look off—especially when applying filters like Macro Segment or Accommodation Revenue Description—this article explains:
Why those differences occur
What’s happening in the background
How to fix it using a simple workaround
✅ Who Should Read This?
This guide is relevant for anyone who:
Uses the Bookings or Pick-up Topics
AND
Analyzes metrics like Occupancy, Capacity, or RevPAR
AND
Applies reservation-level filters in dashboards or workbooks (e.g., segments, rate plans)
🧠 What’s Going On Behind the Scenes?
You may have noticed situations like these:
“Why is capacity decreasing toward the end of the year?"
“Why does occupancy look incorrect when filtering to Overnight stays?"
“Why does occupancy drop when I add segments into my query?”
These issues stem from how Capacity data is joined through reservations. When you apply a reservation-level filter, it behaves exactly as expected on reservations—but can accidentally filter out Capacity values too, which leads to incorrect occupancy calculations.
📉 When Things Go Wrong: Visual Example
Here’s an example where Capacity, Units, and RevPAR are broken down for “Overnight” accommodation revenue:
In the above table:
Capacity drops drastically from month to month, especially in the second half of the year.
Occupancy rates and RevPAR decline even though the hotel likely still had rooms available.
This happens because filtering on "Accommodation Revenue Description = Overnight" limits not just reservations, but the Capacity joined to them.
✅ When Occupancy Works as Expected
When Capacity isn’t filtered out, occupancy is calculated properly:
Notice how Capacity stays consistent across months, even as Units fluctuate. This is the ideal behavior—Occupancy is calculated as:
Occupancy = Units / Total Capacity
⚠️ When Occupancy Breaks Due to Filters
Here’s a breakdown of what happens when a reservation-level filter (in this case, Macro Segment = Contract) is applied:
Capacity is filtered down to just what’s tied to Contract reservations.
Since there are fewer reservations, total Capacity shrinks.
This leads to inflated or misleading Occupancy rates, even though the hotel may have had many unoccupied rooms.
📋 Common Dimensions That Can Cause This
These reservation-level dimensions may trigger this issue if included in a query with Capacity-based measures:
🛠️ Fixing It: The Workaround with XLOOKUP
If you want to use these reservation-level filters but still show accurate Occupancy and RevPAR, follow this approach.
Step 1: Create a Capacity Reference Tab
Create a separate tab with the Capacity values, grouped and filtered as needed.
Align the data in the Capacity tab with the tab where you’ll apply XLOOKUP (e.g., if using "Stay Month," ensure it appears in the same column in both tabs for easy reference).
This tab serves as your "truth" source for Capacity.
Step 2: Use XLOOKUP
In your main query:
Copy the XLOOKUP reference from the Capacity tab.
Add a new column labeled Total Capacity
Use XLOOKUP to bring in values from the reference tab:
=XLOOKUP(A1, Capacity!A:A, Capacity!B:B)
(A1 = Stay Month, Capacity!A:A = Stay Month, Capacity!B:B = Capacity)
Need to reference multiple fields (e.g., Stay Month + Property)?
Step 3: Recalculate Occupancy
Add a column
Add the recalculated Occupancy (use the Natural Language Formula if you’re unsure how to construct it)
= Units ÷ Total Capacity
Format as needed
Step 4: Test & Validate
Reapply filters like Segment, Channel, etc in your workbook or dashboard
Confirm that Occupancy behaves correctly.
Check that Capacity remains steady across filtered states.
Step 5: Clean It Up
Hide helper columns in your tile if needed (if impacting the visualization)
Clearly label the reference tab, adding a tab description, so others know what it’s for
Final Step: Dashboard Setup
When adding these tiles to your dashboard:
Consider which tiles should be visible / hidden on the Dashboard
Clearly label the workbook tabs so it’s consistent and clear which will be hidden
Create your Dashboard
Add your filters
Edit any filters to check which tiles are connected to which filters and update these to work in the intended way with all tiles
✅ Summary: What You’ve Learned
Reservation-level filters can break Occupancy and RevPAR by filtering out Capacity.
This behavior is due to how data is joined in the Bookings topic.
Using XLOOKUP with an unfiltered reference tab allows you to keep your filters and still maintain accurate performance metrics.