Average Ticket Completion Time

[DJsummers]

Hi

I connect to an SQL dataset using the DirectQuery option and the table I am having a problem with is the one in the image attached. IssueNumber, CreatedOn & ResolvedOn are all from IssuesView table.

I have created 3 measures

Created = MAX(IssuesView[CreatedOn])

Resolved = MAX(IssuesView[ResolvedOn])

&

Ticket Duration = DATEDIFF([Created], [Resolved], MINUTE)

I would now like to get the average ticket duration but I am unable to do so. I can't use AVERAGE on my Ticket Duration measure as I get the error 'AVERAGE function only accepts a column reference as the argument number 1'

I have it filtered out for the past 7 days.

Any assistance you guys can offer would be gratefully appreciated as always!

Dan

[DJsummers]

@Greg_Deckler  Greg pulls through for me again! Thanks man, that works a treat. Very much appreciated.

[Greg_Deckler · Accepted solution]

@DJsummers This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.