EazyBI: Don't count 0 in average workdays

JaA February 18, 2019

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]:

average_workdays.png

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?eazybi_zero.png

 

1 answer

1 accepted

1 vote
Answer accepted
Liu.Jinming March 30, 2019

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
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
April 16, 2024

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

Mārtiņš Vanags
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 18, 2024

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

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events