I am successfully using this measure and the report works fine. I assume it is a default measure, but just in case it isnt:
CASE WHEN [Measures].[Transitions from status] > 0 THEN
[Measures].[Workdays in transition status] /
[Measures].[Transitions from status]
END
However, I have just realised that some of the status are transitioned out of automatically by an automation plugin, which usually takes only a second or two, making the numbers look a lot better for some statuses than others.
The transitions are always done by the same user called Jira Automation, how can I exclude this from the report, so that we end up with:
“Average Time in Transition Status but ignore when the transition out of the status is by the user called Jira Automation”
Or am I thinking about this incorrectly and there is a better way of doing it?
Another option would be creating three new calculated measures that exclude the one Transition Author member. This option is more efficient and with better performance. See the formulas below:
Workdays in Transition Status WO:
NonZero(([Measures].[Workdays in transition status],[Transition Author].DefaultMember)
-
([Measures].[Workdays in transition status],[Transition Author].[Michael Scott]))
Transitions from status WO:
NonZero(([Measures].[Transitions from status],[Transition Author].DefaultMember)
-
([Measures].[Transitions from status],[Transition Author].[Michael Scott]))
AVG workdays in transition status WO:
CASE WHEN [Measures].[Transitions from status WO] > 0
THEN
[Measures].[Workdays in Transition Status WO]
/
[Measures].[Transitions from status WO]
END
The first two calculated measures calculate the number of workdays spent and transitions from statuses excluding “Michael Scott”. The third calculates the average. A sample report could look similar to the one below:
On a small data set, the difference in report load times might be negligible, but for bigger accounts, calculated members aggregating several dimension members can increase the report load time. A simple subtraction operation will work faster.