Excluding negative values from calculation

# Excluding negative values from calculation

• 2012年4月17日 上午 12:46

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.

### 所有回覆

• 2012年4月17日 上午 04:09

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

• 已提議為解答 2012年4月18日 上午 09:38
• 已標示為解答 2012年4月18日 下午 10:12
•
• 2012年4月18日 下午 10:12