Time spent (total & avg) 'In Progress' per assignee

Questions & Answers
1

Hello,

I try to present the total and average hours spent by agents on issues, in status ‘In Progress’. The agents are the table rows of the report.

Total hours: an issue may change from ‘In Progress’ to ‘Pending’, back to ‘In Progress’, to ‘Open’ then ‘In Progress’ and finally ‘Resolved’. I want the total time while ‘In Progress’, and while the issue was assigned on the respective assignee of the row.

Average hours: as described above, one issue might “visit” the ‘In Progress’ status for:
2+3+7=12 hours, while another issue for 5+4=9 hours.
The average should not be (2+3+7+5+4+9)/5 = 4,2 hours…
The correct average would be (12+9)/2 = 10,5 hours, because we are interested ‘by issue’.

If the time period of the report is one day, it should only include the issues that were ‘In Progress’ for that day.

Can you help?

2

Hello,

To calculate the average by issue rather than by transition count, use the measure “Transitions from status issues count” in your formula:

CASE WHEN 
   [Measures].[Transitions from status issues count] > 0 
THEN 
  [Measures].[Days in transition status] 
  / [Measures].[Transitions from status issues count] 
END

Sharing this answer here as well, so other community members can benefit from it.

Best,
Ilze