- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Calculating the total occupancy and RevPAR for hot...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Lamia

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
03:11 AM

Hello,

Would you please help me on the below

I am not able to have the total occupancy and RevPAR calculated correctly

Here is the case:

There are 2 Hotels (Properties), with different number of rooms

Prop1 = 300 rooms and Prop2 = 170 rooms

- The occupancy would be the number of total rooms sold (RoomNight) in a given month divided by the capacity of the given property multiple number of days in a month

Occ% = DIVIDE(SUMX(Revenue,Revenue[RoomNight]),[All Rooms])

I used the below measure to calculate the availble rooms per month but this is not given me the correct total per year of even when I select more than one month

All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX(DISTINCT('Calendar'[DaysInMonth]),'Calendar'[DaysInMonth])

- RevPAR is the Revenue per available room. For a given month it would be the total revenue of that month divided per the inventory multiply per number of days of that month

For all this calculation, I can filter by property to have the result either per property or both properties

My issue is that the total per year is not given the correct number. The same issue if I select more than 1 month and this is because my measure “All Rooms” is not given the right total of the months selected

I am sharing the link to the .pbix file if needed

https://filedn.com/lRKl0pgmdmYugsFfu7PUhjL/Test%20Occ%20and%20RevPAR.pbix

Thanks you in advance for your help

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

yelsherif

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
04:41 AM

You then need to change the cross filter direction between Calendar and Revenue tables to be "both" so both tables filter each other. This should make correct calculations for months and total

yelsherif

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
04:41 AM

All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX('Calendar','Calendar'[DaysInMonth])

7 REPLIES 7

yelsherif

Member

Re: Calculating the total occupancy and RevPAR for hotels

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
03:50 AM

You just need to remove the DISTINCT part as follows

All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX('Calendar'[DaysInMonth],'Calendar'[DaysInMonth])

Because DISTINCT will add the unique possible values of days in month, which are 28, 30, 31 = 89 then multiplies the total by the number of rooms in the inventory: 300 x 89 = 26700

Lamia

Frequent Visitor

Re: Calculating the total occupancy and RevPAR for hotels

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
03:59 AM

I tried to remove Disctinct but now the measure is given me an error - please see below

yelsherif

Member

Re: Calculating the total occupancy and RevPAR for hotels

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
04:03 AM

Sorry, I made a mistake, check this one

All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX(Calendar,'Calendar'[DaysInMonth])

Lamia

Frequent Visitor

Re: Calculating the total occupancy and RevPAR for hotels

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
04:09 AM

Now this measure is not giving the correct total per month which should be for examble 300 (capacity) per number of days 31 (for Jan) = 9300 and 8400 for Feb... etc

yelsherif

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
04:41 AM

yelsherif

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
04:41 AM

All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX('Calendar','Calendar'[DaysInMonth])

Lamia

Frequent Visitor

Re: Calculating the total occupancy and RevPAR for hotels

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
04:52 AM

Wow, his is working just fine!!

Thank you very very much !!!