Forum Discussion

Samhunt's avatar
Samhunt
Helper II
2 years ago
Solved

Help with formula

Hi guys, 

 

I used the following formula,

 

Average lead Time =
CALCULATE(
    AVERAGE(Bookings[Booking Lead Time]),
    Bookings[Status] = "Paid" || Bookings[Status] = "Confirmed"
)
 
So what I want is the average Booking lead time for all bookings that have paid or confirmed status. When I check my numbers I get minor differences in the results from what I should get. I dont care if the status is different, I want one number that will include both statuses. So I am not sure if the formula is correct or if there is a problem somewhere else. 
 
Regards
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Samhunt ,

     

    Please try code as below to create a measure.

    Average lead Time =
VAR _STATUSLIST =
    CALCULATETABLE (
        VALUES ( Bookings[Status] ),
        ALLEXCEPT ( Bookings, Bookings[ID] )
    )
RETURN
    CALCULATE (
        AVERAGE ( Bookings[Booking Lead Time] ),
        FILTER ( Bookings, "Paid" IN _STATUSLIST && "Confirmed" IN _STATUSLIST )
    )


    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Samhunt ,

     

    Please try code as below to create a measure.

    Average lead Time =
VAR _STATUSLIST =
    CALCULATETABLE (
        VALUES ( Bookings[Status] ),
        ALLEXCEPT ( Bookings, Bookings[ID] )
    )
RETURN
    CALCULATE (
        AVERAGE ( Bookings[Booking Lead Time] ),
        FILTER ( Bookings, "Paid" IN _STATUSLIST && "Confirmed" IN _STATUSLIST )
    )


    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.