Aggregate Transition Status, where all data is present

Questions & Answers
1

Hello,

Please, help me with the following function.
We have table with ‘Workdays in transition status’, grouped by Transition Status. Per each issue.

image
image1592×330 55.3 KB

Last 4 columns - aggregation of several different transition statuses. Currently, they show total by all presented. So, if only 1 Status from 5 is presented, it will contain value of this 1 Status.
What we’d like to have: Show the value only in case the presence of all Statuses, which participate in concrete aggregation. So, if 4 from 5 are presented - field will be empty until last Status data is present.

The current formula for aggregation:

   Aggregate({
   [Transition Status].[New],
   [Transition Status].[Cost Estimation],
   [Transition Status].[Need Clarification],
   [Transition Status].[Waiting for Priority],
   [Transition Status].[Ready for Development]
   })

How can I do it?

Thanks in advance

2

Hi @Anton_Kruglikov!

Instead of using the Workdays in transition status measure, you can define a new measure that shows the workdays in transition status only if they are filled in for all statuses. Here is a formula that would do this (see comments inline):

CASE WHEN -- when there is more than one status in aggregation
  Count(ChildrenSet([Transition Status].CurrentMember)) > 1 
THEN 
  CASE WHEN -- count if all the statuses have workdays in transition status
    Count(Filter(ChildrenSet([Transition Status].CurrentMember),
      NOT IsEmpty([Measures].[Workdays in transition status])))
    = Count(ChildrenSet([Transition Status].CurrentMember))
  THEN Val([Measures].[Workdays in transition status]) -- if yes, show the workdays
  END
ELSE -- if there is only one status, show it's workdays in transition status
  NonZero([Measures].[Workdays in transition status])
END

Lauma / support@eazybi.com

3

@lauma.cirule, thanks a lot!

1 Like
4

Is there someplace I could get an export definition for a report like that?