Filtering out small values & Statistics on % of low values

Hello there!
I’m working at model life cycle dashboards right now.
One of the metrics is the average workdays spent in status.
It is one of the sample metrics, nevertheless there’s some problems to it.
The problem is, some issues are being forgotten to update by the team, and then they get like 5 status changes in a row, each inside 1 day only. When i drill into issues which sums up to average, there’s like 50% of normal time and 50% of 0 days in transition status.
I need to exclude them from calculatings of average, and i should make a control wich will count the amounts of such cases, total and the % of all issues in status.
And i’m really stuck here.
As far as i understand, i should make new calculated member with filter in it, like avg ( filter( …)), but none of my syntaxis is working(
For the second problem i also can’t really sort out how MDX code should look like(
Would highly appreciate community help.
Thanks in advance!

Hi @Dmitry_Novitskiy

For your use-case, there won’t be a ready-to-go predefined measure as the default “Average workdays in transition status” won’t exclude any of the transitions that were finished (issue left the status).
It doesn’t care how long issue stayed in the source status and if it was normal time or just a 1 day activity.
You would need a custom calculation (by creating a new user-defined formula) that iterates through all issues and exclude those you don’t want to count.

Try something like this for avereage workdays in transition status calculation:

NonZero(
Avg(
DescendantsSet([Issue].CurrentHierarchyMember,[Issue].[Issue]),
CASE WHEN
[Measures].[Transitions from status]>0
AND
[Measures].[Workdays in transition status]>1
THEN
[Measures].[Workdays in transition status]
END
)
)

In a similar (using the Sum function as aggregator) way you can count issues that had these “weird” short transitions:

Sum(
DescendantsSet([Issue].CurrentHierarchyMember,[Issue].[Issue]),
CASE WHEN
[Measures].[Transitions from status]>0
AND
[Measures].[Workdays in transition status]<2
THEN
1
END
)

If you find them slow (because these are complex queries to calcualte results) in your reports, please reach out eazyBI support and provide more details about the layout of your reports. perhaps there can be some ways to improve the calculation steps depending on the report context.

Martins / eazyBI

Martins, you saved my day!
Thank you soo much!
Also i noticed, that when i use "THEN [Measures].[Workdays in transition status] " i am getting numbers higher than when i’m using "THEN [Measures].[Average workdays in transition status] " .

It is because when issue was at that exact status more than once, average calculation would divide number of days by amount of times issue was in that status.
Not really related to my question, but i’m glad i figured it out)

1 Like