Count against multiple criteria and divide against another column value against a single row

[MattLink]

Hi All,

Hoping for some assistance please.  We have work orders that engineers attend to.  The value of the work is held against the work order.  Each work order could have multiple bookings.  So i need to count the number of eligible bookings (based on status) and then divide this number by the value of the work oder.  This is to provide us with accurate daily forecast figures per each engineer.

Hopefully the below example better depicts what I am trying to achieve, with the Booking Value being a custom calculated column.  

At the moment if i use the work order value it presents an incorrect result.

Any help you can provide on any forumla or DAX I can use to get the desired result will be greatly appreciated.

Thank you

[tamerj1 · Accepted solution]

Hi @MattLink 
New Measure 

Booking Value =
VAR WorkIdAndStatus =
    CALCULATETABLE (
        TableName,
        ALLEXCEPT ( TableName, TableName[Work Order ID], TableName[Booking Status] )
    )
VAR NumberOfBookings =
    COUNTROWS ( WorkIdAndStatus )
VAR OrderValue =
    MAXX ( WorkIdAndStatus, TableName[Work Order Value] )
RETURN
    IF (
        SELECTEDVALUE ( TableName[Status] ) = "Completed",
        DIVIDE ( OrderValue, NumberOfBookings )
    )