I would like to create a measure that reports production rate, based on two pieces of information. The first piece of information is a sum of the hours spent in a status of “Working”. The second piece of information is a sum of units of work resolved (in my case this is an existing measure called “Initial Miles resolved”)
The general math would be Production Rate = (sum of hours in working) / (sum of Initial Miles resolved). Pretty straightforward, I’m just not sure of the proper syntax.
Hi,
There are standard measures “Days in transition status” and “Workdays in transition status” which you can combine in the tuple with the Transition status and get the total days for the issues in a status:
([Measures].[Days in transition status],
[Transition Status].[Working])
The challenge with the calculation like you wish to create is to adjust that both parts of the measure (divider and denominator) are calculated from the same scope of issues. As your denominator refers to the “… resolved” measure, the sum of the time in the status should also be based on the same set of resolved issues. That might not be the case if you would use this tuple in the formula since the issue might have spent time in the working status but might not be resolved.
For the complete solution, you need to iterate over the issue set and collect the time in status only for the resolved issues. The exact formula might also require to override the Time dimension if you filter the resolved issues by time:
Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Issues resolved]>0
),
([Measures].[Days in transition status],
[Transition Status].[Working],
[Time].CurrentHierarchy.DefaultMember)
)
/
[Measure].[Initial miles resolved]
Kindly,
Janis, eazyBI support