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.

Chris878's avatar
Chris878
Regular Visitor
1 year ago

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)

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.