Dear eazyBI Community,
one question: I was interested in the dimension “Average workdays in transition status”.
I noticed I had a lot of tickets with 0.00…X days. Of course, the zero values drag down the average and therefore, I wanted to exclude them from analysis.
It is possible to get rid of the zero days as shown in the drill through issues, e.g., by using this formula:
CASE WHEN [Measures].[Average workdays in transition status] > 1 THEN
[Measures].[Average workdays in transition status]
END
However, what ends up happening is: The tickets are not shown in drill-down but the shown average stays exactly the same. Somehow, the calculation from eazyBI seems to not work out here.
Can anyone help out here? Does anyone know, why eazyBI is not calculating what it shows me?
Thanks in advance. Your help is much appreciated.
All the best
Philipp
Hi @pworfel
Welcome to the eazyBI community!
Usually, an average time of 0.00 means that the issue has been transited through the status in a very short period (rounded 0.00 is for everything less than approximately 7 minutes). However, the transition has happened, so the average calculation must include those zeros as valid.
If the transition has not happened, the value is empty and the issue is not included in the average calculation.
If you still want to remove those zeros from the average, you must iterate through each issue and take it into the average calculation only if the time spent in status is larger than 0.0049 (i.e., the value that rounded would be 0.01 and more):
Avg(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
CASE WHEN
[Measures].[Workdays in transition status]>0.0049
THEN
[Measures].[Workdays in transition status]/
[Measures].[Transitions from status]
END
)
Iterating through issues is slow. According to the context of the report, this calculation might be optimized.
Best,
Ilze, support@eazybi.com