Average workdays in transition status exclusion

Hi all,

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?

Thanks in advance

Figured this out by using “Transition Author” calculated member

Hi @auto98 ,

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.

Best,
Roberts // support@eazybi.com