Hi everyone,
My case:
i'm calculation order duration based on various fields and am storing them in various other fields, more or less like this:
If(([Status]= '512'), (Today()-[OrdStartDate]))as OrdDurationActive,
If(([Status]= '1024'), ([OrdRS1024Date]-[OrdStartDate]))as OrdDurationWon,
I think I need to use this method because the duration is based on various fields. In practice, this leads to 4 nulls and one filled value, because based on the status, only one of 5 fields is filled. It isn't possible for more than one field to have a value.
This results in 5 different fields for different states. Now I want to create one average based on these various fields.
So in this case:
ID | Status | Active Duration | Won Duration
Order 1 | Won | - | 200 |
Order 2 | Active | 123 | - |
The average of these two should be (200+123)/2
How should I achieve this? I've played around with Sum(Avg(..)+Avg(..)), et cetera, but didn't get the right results yet...
