locked
Excluding negative values from calculation RRS feed

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

    Tuesday, April 17, 2012 12:46 AM

Answers

  • 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 Challen Fu Wednesday, April 18, 2012 9:38 AM
    • Marked as answer by Sakic21 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 Challen Fu Wednesday, April 18, 2012 9:38 AM
    • Marked as answer by Sakic21 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