cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jernau Frequent Visitor
Frequent Visitor

Measure to average duration in related rows

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 NumberTaskDurationParentTaskDesired Duration Measure Output (average duration)
MCN51T10P11
MCN51T20P23
MCN51P11<blank><blank> or 0
MCN51P23<blank><blank> or 0
Total  4 2

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft v-jiascu-msft
Microsoft

Re: Measure to average duration in related rows

Hi @jernau,

 

Please try the formula below.

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

Measure_to_average_duration_in_related_rows

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Microsoft v-jiascu-msft
Microsoft

Re: Measure to average duration in related rows

Hi @jernau,

 

Please try the formula below.

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

Measure_to_average_duration_in_related_rows

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

jernau Frequent Visitor
Frequent Visitor

Re: Measure to average duration in related rows

That worked really well, thank you Dale!

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)