Calculate an Average between two dates with additional selection

[Anonymous]

Hi All,

I have a table with Categories and open dates and closed dates, i've created a measure to calculate the time difference between the open and closed using:

Time to Resolution (Minutes) = DATEDIFF(MIN('deskpro ticket'[timestamp_opened_dt]),MAX('deskpro ticket'[timestamp_closed_dt]),MINUTES)

I've then calculated an overall average using:

TTRAVG = averagex('deskpro ticket',('deskpro ticket'[timestamp_closed_dt]-'deskpro ticket'[timestamp_opened_dt]))*24*60

My Tabel headers are Ticketid/Category/Timeopened/Time Closed/Time to resolution

I now need to create an average time to resolution by category, i'm unsure how to do that, any suggestions?

Thanks,

Iain

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for reaching out to us.

You can use this measure, I also create a sample for your reference,

AverageByCategory = 
    var _sumCategory=SUMX(ALLEXCEPT('deskpro ticket','deskpro ticket'[Category]),[Time to Resolution (Minutes)])
    var _count=COUNT('deskpro ticket'[Category])
return _sumCategory/_count

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly

[Greg_Deckler]

@Anonymous 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.