Forum Discussion

Chris878's avatar
Chris878
Regular Visitor
1 year ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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

 

Chris878's avatar
Chris878
Regular Visitor
1 year ago

"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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.