Hello,
I would like to ask for help on this issue.
I am looking to create a calculated column that will give me the number of rooms in a consequent order based on the following context:
hotel start date
hotel end date
hotel room type code
hotel name
Here is how I would like to see it:
or in a more simpler view:
I approached the case first by using RANKX and the column passenger_group_id for providing the correct consequent room number.
The column ***No_of_conseq_rooms_by_passenger_group_id is the result. However for bookings where the count of passenger_group_id is more than 2 it gives me wrong number of rooms.
The example below shows the issue - for booking 4244226, we have one group ID 2400304 and 4 different passengers. They are going to accommodate Twin room, so this should be 2 twin rooms
After realising that this approach will not solve me the issue, I asked and got an extra column (from our data engineers) that calculates the ratio of max_occupancy pr room and the number of passengers pr booking. This results in the column no_of_rooms_calculated.
And here is the issue:
I would like to get to the result shown in the column “How I want to see it”, based on the column “No_of rooms_calculated”
Any help is highly appreciated!
Thanks,
Tyankata
update by 25. Aug. 23, 15:00 CET
I realized that I could do a calculated column based on a change in the passenger_ID:
*** No_of_rooms_indexed =
VAR Passenger_ID_index = fact_tbl[passenger_id]
RETURN
CALCULATE(
SUM(fact_tbl[no_of_rooms_calculated]),
FILTER(
fact_tbl,
fact_tbl[passenger_id] <= Passenger_ID_index)
)
And then create an Index column - “IndexHotelContext” based on Hotel Start date, Hotel End Date and Hotel Room type code - created in the Power Query.
And now I need to combine both columns in a single calculated column to get to the result needed.
Any help is still highly appreciated.
Hotel rooms ranking.pbix (2.7 MB)