Average based on another field

Hello guys

I have used below formula to calculate the days between two statuses. and it working correctly for every single issues.

DateDif =
DateDiffDays(
([Measures].[Transition to status first date],
[Transition Status].[Call to interview],
[Transition].DefaultMember,
[Time].CurrentHierarchy.DefaultMember),
([Measures].[Transition from status first date],
[Time].CurrentHierarchy.DefaultMember)

but i want the average of this measure, based on another customfiled (single select list) value.

image

and i want below result

Stack A = 2
Stack B = 2.25
Stack C = 4

Hi

You can use the Avg function for calculating the aggregated average:

Avg(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  DateDiffDays(
([Measures].[Transition to status first date],
[Transition Status].[Call to interview],
[Time].CurrentHierarchy.DefaultMember),

([Measures].[Transition from status first date],
[Transition Status].[Call to interview],
[Time].CurrentHierarchy.DefaultMember)
)>0
  ),
  DateDiffDays(
([Measures].[Transition to status first date],
[Transition Status].[Call to interview],
[Time].CurrentHierarchy.DefaultMember),

([Measures].[Transition from status first date],
[Transition Status].[Call to interview],
[Time].CurrentHierarchy.DefaultMember)
)
)

A couple of further comments, however.

It seems that your formula excerpt is missing some parts. If you use the same status for the start and end date, perhaps, the standard measures of Average days in transitions status should do. The standard measure will be much more efficient.

You may also consider implementing your use case with the fresh feature of the custom cycles: Issue cycles

Kindly,
Janis eazyBI support