eazyBI: return Average and Max calculation only one time for all issue's project

[Reply Test Group]

Hi Jira and @eazyBI Support,

I created a report that shows, for every issue in a project, my (custom) field Lead Time in seconds using the Measure:

    [Issue].CurrentHierarchyMember.get('Lead Time in seconds')

, but I want to show the average and the max value too, for those values.

Using the 'Add calculated' > 'Statistical' > 'Average' / 'Max' tools, I obtained the values but listed in every rows:

I would like to insert the Average value in the yellow slot and the Max value in the orange one. It is possible?

Or if not, there is a way to report this value only one time for a project? 

Thank you!

[Jordan Janis · Accepted solution]

@Reply Test Group 

You should be able to create a calculated measure that performs the math (avg, max, etc.) only at the Project level in the hierarchy.

For example:

CASE WHEN
   [Issue].CurrentHierarchyMember.Level.Name = "Project"
THEN
   AVG(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      [Measures].[Issue Lead Time in Seconds]
   )
END

Another option that may be more useful is to only calculate the AVG for the visible rows in the table.  This is probably more accurate depending on how you're displaying your report.  That would look like this:

CASE WHEN
   [Issue].CurrentHierarchyMember.Level.Name = "Project"
THEN
   SUM(
      VisibleRowsSet(),
     [Measures].[Issue Story Points]
   )
END

See if either of those works for you.

Regards,

-jj

[Reply Test Group]

Thank you @Jordan Janisfor your help, those codes saved me! 

But now I have another question.. can I combine those codes in just one measure?

I explain myself: 

I created a report with 2 similar measures for Average and Max: 

 - AVG/MAX Lead/Exec Time in Seconds (Issue Visible) -> using Descendants function (yellow)

 - AVG/MAX Lead/Exec Time in Seconds (Project) -> using VisibleRowsSet() function (orange)

From the screen, you see that when the project cell "FIN-IMPROVEMENT" is exploded, the measures are the same:

But when I implode the project cell, you see that the yellow cells are blank, while the orange ones remain correctly valued:

I want to know if there is a function in eazyBI for checking if the [Issue].CurrentHierarchyMember (in this case, the project cell FIN-IMPROVEMENT) is imploded or exploded, for creating an unique column for both the codes.

Thank you again, bye!

[Jordan Janis]

Hi,

It looks like the issue you're having here is in the nature of the two measures you created.

One uses Descendants() and one uses VisibleRowsSet().

VisibleRowsSet will only work for rows that are visible on rows.  So, when you collapse your rows there are no rows visible and, thus, the result is an empty cell.

VisibleRowsSet() works well if you want to show all of the rows in your report/chart, along with the summary values.

Descendants() (or something like Descendants()) is better if you intend on only displaying the roll-up/summary results and hiding the rows.

My point in my original comment was that Descendants will include ALL members under the specified hierarchy member.  Since you're using Issues for your Rows you may have thousands and thousands of them which can hurt performance.  You may want to include a Filter() to limit this set depending on the nature of the report.  For instance, I have reports that return the latest 6 sprints from a board - as opposed to ALL sprints, which is what Descendants would do if not filtered.  Here's the eazyBI documentation for Descendants().  A couple of examples are given which should help explain it more clearly. https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/descendants

There are other ways to limit the number of rows in the report.  This is just an option.

Hope that helps!

-jj

[Reply Test Group]

Hi @Jordan Janis

a few days have passed but I have not forgotten your answer ^^

I found a solution that could be useful to other people as well, but it might not necessarily be the most efficient.

CASE WHEN [Issue].CurrentHierarchyMember.Level.Name = 'Project' THEN
IIF(
  IsEmpty(
    AVG(
      VisibleRowsSet(),
      [Measures].[Lead Time (FIN)]
    )
  ),
  AVG(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Lead Time (FIN)]
  ),
  AVG(
    VisibleRowsSet(),
    [Measures].[Lead Time (FIN)]
  )
)
END

Practically, this measure works both when the row is left closed and when it is exploded (the bold part does the check).

The solution appears like this when imploded:

, and like this when exploded:

In case anyone has a way to improve or speed up this report, I hope they show up!

Bye !

-FF-

[Daina Tupule eazyBI]

eazyBI offers great options with new default calculations. They might be slightly limited when you have all the visible rows. In many cases, like this one as well. You would like to build overview reports.

Averages on custom numeric fields

eazyBI allows importing any numeric field as a measure and as property. Each customfield will have several respective measures based on the activity in issue (Lead time created, Lead time resolved). Those measures will give you SUM by default. Property (starts with singular Issue - Issue Lead time, shows values but will not give SUM by default.

eazyBI will generate additional hidden measures for some default activities Issues with lead time created counting issues with lead time by created date. You can use it for Average calculation:

[Measures].[Lead time created] / 
[Measures].[Issues with Lead time created]

Min, Max, etc. on numeric fields
Min, Max, Stdev, Percentile, Avg (if the previous solution is not suitable), etc., requires a set of issues. In this case, we suggest using Descendants as quite a general solution. Function Descendants over a set of issues will calculate values per individual issue and for any other higher level (Project, All issues).

Any calculation on a set of issues might work slow. Therefore, we suggest using some filters by issues properties (typically by filtering time) and using some measure to reflect the report's dimension selections.

Here is a very general formula for picking up issues created in the selected period and Max value in the custom field Lead time.

Max(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember
)
),
[Measures].[Lead time created]
) 

 Measure Lead time created will work here as a numeric argument for any function, and as a filter by any selection (Time, Issue type, Status, etc.) on pages, rows, and columns.

Issue created date and Lead time created both will work as a filter by issue creation date on time.

In this Boxplot chart, we have lead time calculations based on default field resolution days. It has several formulas (check when switched to a table) using similar principles as described above.

Daina / support@eazybi.com

[Reply Test Group]

Thank you @Daina Tupule eazyBI 

I'll test the measure with Filter too, for speeding up the reports.

Bye !

-FF-