Forum Discussion
Penetration rate in complex dataset
Hello all,
i am currently building a quite complex/powerful reporting dashboard to monitor pre-booked revenues of hotel guests. I would like to calculate a penetration rate, meaning a percentage of how many of the booked customers have made pre-booked revenue in a specific time period for a specific product range.
The data source is a transaction table which has a time period timestamp "Load date" and lists every transaction in one line. the dataset is quite big (close to 20m rows), so i need a performant solution. Guests have a unique ID called "GuestID". in the collumn "Guest Booked" they receive a "1" at the time of booking and a "-1" when they cancel again. These are two of the transaction lines. The pre-booked revenue for on top services can be booked and cancelled again throughout any time of the pre-booking phase. they are listed in seperate transaction lines, also with the Load date and the rrespective "GuestID"
I would like to build following formula in the end:
Calculate(Sum(Guest Booked), Sum(Pre-Booked Revenue)>0, Load Date <= MAX(Date Parameter))
The calculation needs to be basically on the aggregated level of the Guest ID but also not since i need the filter options for the product category and the load date to replicate older data status (e.g. as of prior week or 6 weeks ago)
I was experimenting with the Summarize function, however as calculated table i was not able to have the filter options applicable. as calculation in a measure the performance was an issue and the calculation would outrun ressources.
I hope anyone has an idea.
Hi Chris878,
Thank you for reaching out in Microsoft Community Forum.
Please follow below steps to measure for the 1 and 2 stpes;
1. Measure for Booked Guests with Net Booking > 0 up to selected date
BookedGuests_Net =
CALCULATE(
DISTINCTCOUNT('TransactionTable'[GuestID]),
FILTER(
ADDCOLUMNS(
SUMMARIZE('TransactionTable', 'TransactionTable'[GuestID]),
"NetBookings",
CALCULATE(
SUM('TransactionTable'[Guest Booked]),
'TransactionTable'[Load Date] <= MAX('DateTable'[Date])
)
),
[NetBookings] > 0
)
)2.Measure for Guests with Net Pre-Booked Revenue > 0 up to selected date
GuestsWithNetPreBookedRevenue =
CALCULATE(
DISTINCTCOUNT('TransactionTable'[GuestID]),
FILTER(
ADDCOLUMNS(
SUMMARIZE('TransactionTable', 'TransactionTable'[GuestID]),
"NetRevenue",
CALCULATE(
SUM('TransactionTable'[Pre-Booked Revenue]),
'TransactionTable'[Load Date] <= MAX('DateTable'[Date])
)
),
[NetRevenue] > 0
)
)3.Final Penetration Rate Measure;
PenetrationRate =
DIVIDE(
[GuestsWithNetPreBookedRevenue],
[BookedGuests_Net],
0
)Please continue using Microsoft Community Forum.
If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.
Regards,
Pavan.
8 Replies
- bhanu_gautam
Super User
Chris878 Create a Measure for Pre-Booked Revenue
PreBookedRevenue = SUM('TransactionTable'[Pre-Booked Revenue])
Create a Measure for Booked Guests
dax
BookedGuests = CALCULATE(
SUM('TransactionTable'[Guest Booked]),
'TransactionTable'[Load Date] <= MAX('DateTable'[Date])
)Create a Measure for Guests with Pre-Booked Revenue:
dax
GuestsWithPreBookedRevenue = CALCULATE(
DISTINCTCOUNT('TransactionTable'[GuestID]),
'TransactionTable'[Pre-Booked Revenue] > 0,
'TransactionTable'[Load Date] <= MAX('DateTable'[Date])
)Create the Penetration Rate Measure:
dax
PenetrationRate = DIVIDE(
[GuestsWithPreBookedRevenue],
[BookedGuests],
0
)Ensure that your visualizations or calculations are filtered by the product category and load date as needed. You can use slicers or filters in Power BI to achieve this
"DISTINCTCOUNT('TransactionTable'[GuestID])" doesnt work since the guest can cancel his booking again and only guests with a booking at the current data status should be included.
"'TransactionTable'[Pre-Booked Revenue] > 0" doesnt work since i need the sum of all transactions until that certain time. This can include for example that re made 119 USD revenue and then cancels the revenue at a later time stamp (-119 USD rvenue). In that case i don't want to count him since in total he has no revenue made anymore.Hi Chris878,
Thank you for reaching out in Microsoft Community Forum.
Thank you bhanu_gautam for the helpful response.
Please follow below steps to acheive the error;
1.Create a measure that counts guests with net bookings > 0 (i.e., more bookings than cancellations) up to the selected snapshot date.
2.Create another measure that counts only those booked guests whose total pre-booked revenue > 0 up to that same date (ignoring refunds/cancellations).
3.Divide the number of guests with net revenue by the total active booked guests.
Please continue using Microsoft community forum.
If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.
Regards,
Pavan.
Hi Chris878,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept as Solution" and give it a 'Kudos' so others can find it easily.
Thank you,
Pavan.Hi Chris878,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept as Solution" and give a 'Kudos' so other members can easily find it.
Thank you,
Pavan.Hey, how would your measure for the 1) and 2) look like? I still have the issue that there are several rows for each guests with bookings and cancellations of pre-booked items. And as far as i know, i cant do something like Calculate (Count(GuestIDI),SUM(Pre-bookings per guest until a certain time stamp)>0)
Hi Chris878,
Thank you for reaching out in Microsoft Community Forum.
Please follow below steps to measure for the 1 and 2 stpes;
1. Measure for Booked Guests with Net Booking > 0 up to selected date
BookedGuests_Net =
CALCULATE(
DISTINCTCOUNT('TransactionTable'[GuestID]),
FILTER(
ADDCOLUMNS(
SUMMARIZE('TransactionTable', 'TransactionTable'[GuestID]),
"NetBookings",
CALCULATE(
SUM('TransactionTable'[Guest Booked]),
'TransactionTable'[Load Date] <= MAX('DateTable'[Date])
)
),
[NetBookings] > 0
)
)2.Measure for Guests with Net Pre-Booked Revenue > 0 up to selected date
GuestsWithNetPreBookedRevenue =
CALCULATE(
DISTINCTCOUNT('TransactionTable'[GuestID]),
FILTER(
ADDCOLUMNS(
SUMMARIZE('TransactionTable', 'TransactionTable'[GuestID]),
"NetRevenue",
CALCULATE(
SUM('TransactionTable'[Pre-Booked Revenue]),
'TransactionTable'[Load Date] <= MAX('DateTable'[Date])
)
),
[NetRevenue] > 0
)
)3.Final Penetration Rate Measure;
PenetrationRate =
DIVIDE(
[GuestsWithNetPreBookedRevenue],
[BookedGuests_Net],
0
)Please continue using Microsoft Community Forum.
If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.
Regards,
Pavan.
Hi Chris878,
I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.
Please continue using Microsoft community forum.
Thank you,
Pavan.
