2012年4月17日 上午 12:46
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.
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.
Elapsed_Time: =IF(AND([COMPLETED_DT_TM]>[START_DT_TM]),NOT(ISBLANK([START_DT_TM]))), 24*([COMPLETED_DT_TM]-[START_DT_TM]), BLANK() )
Avg_Elapsed_Time: =AVERAGEX(Sheet1, Sheet1[Elapsed_Time])
2012年4月18日 下午 10:12
Thanks for your reply, much appreciated - works like a charm.