Average time in transition period

Hi, could, please, someone explain me, how ir is correct to calculate sum of average (mostly mathematical questions)

Given:
Eazy BI example about cycle time:
https://eazybi.com/accounts/1000/cubes/Issues/reports/499777-average-lead-and-cycle-time
We calculate average time in IN PROGRESS category.
IN PROGRESS category contains 2 statuses: IN PROGRESS and REVIEW.

In tables I see, that average days in IN PROGRESS status is 6.16 and
average days in Review status is 2.35

Question: why average days in IN PROGRESS category is 5.93?
Should is not be 6.16+2.35 = 8.51?

I see the formula of how Average days are calculated,

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

so it depends on transitions count. Does it mean, that if all status transitions are done only in one direction (from left to right), and no status is skipped, then transition count for 1 issue always is 1).?
But in case one or more statuses are skipped (less transitions are done), then “average days in Category” according to the formula should be MORE than a sum all statuses in this category.

Interesting, in my projects (checked 4 of them ) average IN PROGRESS category is always less than Sum of the average of each status IN PROGRESS category. And I still don`t understand why…

Could please someone explain, how it works?

Hi @Karina_Paula-Freiman,

You have pretty much figured it out yourself. The “Average days in transition status” calculates the average duration of each transition; it is calculated as the time in selected transition statuses and divided by the count of status changes.

CASE WHEN [Measures].[Transitions from status] > 0 THEN
  --time in selected transition statuses
  [Measures].[Days in transition status] /
  --divided by statute changes from selected transition statuses
  [Measures].[Transitions from status]
END

If you would like to get the average duration in all “In Progress” statuses for each issue, then you should use a slightly different expression. Use the measure “Transitions from status issues count” to divide time in transition statuses with the issue count, not the transition count.

CASE WHEN [Measures].[Transitions from status] > 0 THEN
  --time in selected transition statuses
  [Measures].[Days in transition status] /
  --divided by issue count with statute changes from selected transition statuses
  [Measures].[Transitions from status issues count]
END

The average calculation consists of adding up (+) and dividing (/), and the order of mathematical operation matters. The sum of the average, in most cases, won’t match the average of the aggregate. All the “In Progress” category transition statuses do not have the same weight (different sets of transitions); some of those transition statuses have more movement (more extensive set of issues and transitions) and some less (only a few transitions). The sum of averages wouldn’t give a trustable result if larger and smaller sets are treated equally.
eazyBI recalculates the average for aggregated results like “In Progress” category, not adding up the average of individual transition statuses.

Best,
Zane / support@eazyBI.com