Help with formula

[Samhunt]

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 · Accepted solution]

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.