# 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.

Tuesday, April 17, 2012 12:46 AM

• 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]) ```

• Proposed as answer by Wednesday, April 18, 2012 9:38 AM
• Marked as answer by Wednesday, April 18, 2012 10:12 PM
Tuesday, April 17, 2012 4:09 AM

### All replies

• 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]) ```

• Proposed as answer by Wednesday, April 18, 2012 9:38 AM
• Marked as answer by Wednesday, April 18, 2012 10:12 PM
Tuesday, April 17, 2012 4:09 AM
• Thanks for your reply, much appreciated - works like a charm.

Wednesday, April 18, 2012 10:12 PM