EazyBI: Don't count 0 in average workdays

[JaA]

Hi,

I try to calculate the average workdays an issue was in progress. In my columns I have the default [Measures].[Average workdays in transition status] and [Transition Status].[In Progress]:

When I drill into a month, there are several issues with a time of 0.00. Is there a way to filter these, so that I only have values from 0.01 and higher?

[Liu.Jinming · Accepted solution]

You can write a new caculated member 

Average workdays in transition status1 and use it

CASE WHEN [Measures].[Average workdays in transition status] < 0.01 THEN
NonZero(0)
ELSE
[Measures].[Average workdays in transition status]
END

[Grace McLaughlin]

And how would you then reflect the new average on a bar chart? As it is still including the 0 values.

[Martins Vanags -eazyBI-]

Hi @Grace McLaughlin 

Try this formula for your calculated measure (and select "decimal" output):

CASE WHEN
[Measures].[Transitions from status]>0
THEN

Sum(
DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
CASE WHEN [Measures].[Average workdays in transition status] >= 0.01 THEN
[Measures].[Workdays in transition status]
END
)
/
Sum(
DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
CASE WHEN [Measures].[Average workdays in transition status] >= 0.01 THEN
[Measures].[Transitions from status]
END
)

END

Note it will make the calculation significantly slower due to the complexity of the calculation steps. This new calculation iterates through all imported issues twice per row.

Martins / eazyBI