Measure to average duration in related rows

[jernau]

Hello,

I want to create a measure which will calculate the average duration of task(s) completed by a machine.

There are two task types: 'Task', and 'Parent Task'.  Every 'Parent Task' has one 'Task'.

All of the data I require is held against the 'Task', except for one column which is recorded against the 'Parent Task'.

What I want to achieve, looks like this (the right-most column); can anyone suggest how to do this?

Machine Number	Task	Duration	ParentTask	Desired Duration Measure Output (average duration)
MCN51	T1	0	P1	1
MCN51	T2	0	P2	3
MCN51	P1	1	<blank>	<blank> or 0
MCN51	P2	3	<blank>	<blank> or 0
Total		4		2

[v-jiascu-msft · Accepted solution]

Hi @jernau,

Please try the formula below.

Measure 2 =
AVERAGEX (
    'Table1',
    CALCULATE (
        AVERAGE ( Table1[Duration] ),
        FILTER ( ALL ( Table1 ), Table1[Task] = EARLIER ( Table1[ParentTask] ) )
    )
)

Best Regards,

Dale

[jernau]

That worked really well, thank you Dale!