How To Aggregate a Calculated Member

Questions & Answers
1

Hello,

I am calculating Cycle Time based on our organization’s pre-set fields, Cycle Time Start and Cycle Time End.

Cycle Time =
DateDiffDays([Measures].[Issue Cycle Time Start], [Measures].[Issue Cycle Time End])

This calculation gives me a Cycle Time for every issue that has both a cycle time start & end.

I am trying to create an AVERAGE CYCLE TIME field. Basically, I want the average of Cycle Time, so I can use it in the overview at the top of my dashboards, showing what the OVERALL average cycle time is for the issue type the report is filtered to.

I have tried:

Avg([Measures].[Cycle Time])

I have tried:
Aggregate([Measures].[Cycle Time])

Average Cycle Time is blank for me every time, unless I go to a specific issue. I need it to give me the average cycle time grouped up. So if I’m only looking at projects, I want the average cycle time for that project. Then if I click into that project, i’m able to see the cycle time for each.

Any advice here? I need to be able to report the AVERAGE cycle time in my reports, but EazyBI isn’t giving me an easy way to average my field ‘Cycle Time’. This seems like it should be a simple calculation but I am struggling. Any help would be amazing.

Thank you,
Dani

2

I’ve since been able to get a correct Cycle Time measure for each issue using this:
AVG(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Cycle Time]
)

BUT, it does not change when filtering. Here, I am including ALL projects:

image
image1417×227 18.9 KB

Now when I filter to a project:

image
image1414×236 17.8 KB

The average cycle time is still 17! Which is not correct, and all the other calculations changed upon that filter, so why isn’t my cycle time calc filtering too?

Any help would be really appreciated. I’ve googled so many things and tried a lot of different things with no avail.

3

Hi @dani
​
​Thanks for sharing your use case! For your formula to work at Project level, you have already correctly found Descendants function, which allows you to specify the level (Issue) that the formula should iterate through and capture the required information. However, you have not indicated any Measure in your formula, hence it doesn’t work as expected (ground rules about calculated measures Calculated measures and members).
​
​Please try the formula below and see if it now returns the expected data
​

​Avg(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
  not IsEmpty([Measures].[Issue Cycle Time Start]) AND
  not IsEmpty([Measures].[Issue Cycle Time End])
  ),
CASE
WHEN
[Measures].[Issues with Cycle Time End] >0
THEN
DateDiffDays([Measures].[Issue Cycle Time Start], [Measures].[Issue Cycle Time End])
END
)

​
​Alternatively, I also recommend considering Issue Cycles option for your use case. If your Cycle Time Start and Cycle Time End are defined based on certain statuses the issues are moved, Issue Cycles option might be another solution for your use case. When importing the necessary Cycle, eazyBI will automatically create several measures, including the Average cycle time measure.
​
​Best wishes,
​
​Elita from support@eazybi.com

1 Like