cancel
Showing results for
Search instead for
Did you mean:
Lamia Frequent Visitor

## Calculating the total occupancy and RevPAR for hotels

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
yelsherif Member

## Re: Calculating the total occupancy and RevPAR for hotels

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

## Re: Calculating the total occupancy and RevPAR for hotels

All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX('Calendar','Calendar'[DaysInMonth])
7 REPLIES 7
yelsherif Member

## Re: Calculating the total occupancy and RevPAR for hotels

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

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

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

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

## Re: Calculating the total occupancy and RevPAR for hotels

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

## Re: Calculating the total occupancy and RevPAR for hotels

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

## Re: Calculating the total occupancy and RevPAR for hotels

Wow, his is working just fine!!

Thank you very very much !!!