Forum Discussion

justivan's avatar
justivan
Icon for Helper II rankHelper II
5 years ago
Solved

Targets and Payouts - Design Table

Hello PBI Community,

So I have this table made up of hotel IDs where we have a specific 'room nights' targets up to 4 tiers. They are all different depending on the agreement with our partner some have only 1 tier. The goal is to identify the percentage of where we are currently at in terms of percent achieved. What I did was get the room nights per hotel then divide it per tier. Now my question is, is this the correct approach or is there a more efficient way accomplish my goal?

Measure Tier 1

 

 

=IF (
    SUMX ( ReservationList, [Total Room Nights] / RELATED ( HotelList[T1] ) ) > 1,
    1,
    SUMX ( ReservationList, [Total Room Nights] / RELATED ( HotelList[T1] ) )
)

 

 

Measure Tier 2 ( dividing to an empty value returns an error and if Tier 1 is not yet achieved there's no point to show the percentage achieved for tier 2 hence the 'N/A' and --.

 

 

=IF (
    ISERROR ( SUMX ( ReservationList, [Total Room Nights] / RELATED ( HotelList[T2] ) ) ),
    "N/A",
    IF (
        [Tier 1] < 1,
        "--",
        IF (
            SUMX ( ReservationList, [Total Room Nights] / RELATED ( HotelList[T2] ) ) > 1,
            1,
            SUMX ( ReservationList, [Total Room Nights] / RELATED ( HotelList[T2] ) )
        )
    )
)

 

 

Here's a snapshot of my hotel list table

Here's a snapshot of the report itself

What I'm thinking of doing is to transform my table but im not sure how to approach it this way and if it's the correct way.

 

Need Help

4 Replies

  • m3tr01d's avatar
    m3tr01d
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    Hi justivan ,
    are Target Tier (T1, T2, ...) dependant on the hotelId or are they based on a Group or Category?

    • justivan's avatar
      justivan
      Icon for Helper II rankHelper II
      5 years ago

      Hi m3tr01d,

      Yes they are different per HotelID. May I know what you have in mind in both scenarios you mentioned?

  • m3tr01d's avatar
    m3tr01d
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    Hi justivan,

    I would unpivot your Target / Payout table to have this format instead : 
    HotelId | Tier | Value | Payout

    If you use this format, your "RELATED" function won't work because now, you won't be able to have a Many to one relationship from your Reservation and HotelList.

    I would make a new table containing only the information for Hotel where you would have one row per HotelId.
    With that, you have a model with 2 Fact table (Reservation and Target/Payout) and a dimension Hotel.


     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi justivan,

    For your requirement, I'd like to suggest you enter to query editor and do unpivot column on these tier fields, the table will be summary to raw category and attire and value with all 'tier' values.

    Unpivot columns (Power Query) - Excel (microsoft.com)
    You can use this summary table 'Hotel ID' and attire as matrix row and column, then write a measure formula to lookup correspond rates based on tier level. (extract the 'T' of current value and replace it as 'payout')

    SUBSTITUTE function (DAX) - DAX | Microsoft Docs
    Regards,

    Xiaoxin Sheng