Excluding negative values from calculation

Question

Hi,

I'm trying to add a measure that calculates the average but am getting stuck on how to exclude those rows that don't have a value in both fields required for the calculation. For example, say I want to calculate the average elapsed time, in hours, between a start and an end point; how can I calculate the average but only use those rows where both COMPLETED_DT_TM and START_DT_TM have recorded values and where the difference between the fields is greater than zero?

AVERAGEX(Sheet1, 24. * (Sheet1[COMPLETED_DT_TM] - Sheet1[START_DT_TM]))

includes elapsed times equal to or less than zero which I don't want to include.

Thanks for your help.

Answer 1

I think the way to go here is to create a calculated column on sheet1 that calculates the elapsed time given your conditions, then create a measure that takes the average.

Calculated column:

Elapsed_Time:
=IF(AND([COMPLETED_DT_TM]>[START_DT_TM]),NOT(ISBLANK([START_DT_TM]))),
    24*([COMPLETED_DT_TM]-[START_DT_TM]), 
    BLANK()
)

Measure:

Avg_Elapsed_Time:
=AVERAGEX(Sheet1, Sheet1[Elapsed_Time]) 

Answer 2

Thanks for your reply, much appreciated - works like a charm.