ITSM MTTR/ATTR KPI Visuals

[Anonymous]

I have raw data from our ITSM tool with the following columns:

Key (Ticket Number)

Assignee

Assignment Group

Status

Created (Date and Time ticket was created)

Resolved (Date and Time ticket was resolved)

Categorization

Request Type (Incident or Request Task)

I am having issues creating a visual that shows the average time to resolve for incidents and requests separately. I would like have a dedicated ATTR visual that reports per Assignee, Per Assignment Group. 

Any help would be greatly appreciated

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create column and measure like DAX below.

Column: 
Diff= DATEDIFF([Created], [Resolved], DAY)


Measure:
Average Time= DIVIDE(SUM([Diff]), COUNT([Request Type]),0)

Best Regards,

Amy 

Community Support Team _ Amy

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

[amitchandak]

@Anonymous can you create a column Like

days = datediff([created],[resolved],day)

And take an Average of that column.

Or you can have measure but there you need a row context to forced

days = datediff(min(Table[created]),Max(Table[resolved]),day)

Refer to this blog how row context has been forced

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[lbendlin]

Since you have Created Date and Resolved date in the same row of your data it's as simple as adding a calculated column that subtracts the timestamps.  (Of course this opens a whole 'nother can of worms when you start thinking about office hours, weekends, holidays etc)

Once you have that you can average by category any way you want.