Average value for a row considering page filter

Questions & Answers
1

Hi,

I have a jira custom text field “Avg Memory” and want to calculate average value per another parameter “App version” so average value per row is present.

image

On the picture, each version contains multiple tickets which has values for “Avg Memory” field. I’m trying to calculate average value for “Avg Memory” so green blocks are filled in with average values per category.

I found a possible solution with the following query but the issue is that page level filters and row value with app version are not considered so average value is calculated overall all tickets in jira:

Avg(
   Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
   [Measures].[Avg memory]
)

“Avg Memory” custom field contains numeric data among others so there is not ability to import it in proper way.

Could someone please help with ideas or solutions?

2

Hi,

The formula needs adjustment for the page filters to take effect:

Avg(
   Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
   [Measures].[Issues created]>0),
   [Measures].[Avg memory]
)

This example formula will filter issues according to page filters; the Time filter will select issues created in the selected period.

If the Memory value must be extracted from a text field, an additional formula should be used to match the numeric value from the text and translate it to a number.

Kindly,
Janis, eazyBI support

1 Like
3

Hi Janis,

That solution works, thank you for the reply.
Please pay attention that comma is missing in your formula. So I use the following:

Avg(
   Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
   [Measures].[Issues created]>0),
   [Measures].[Avg memory]
)

As for formula to parse string to numeric value, I use the following that works fine:

[Measures].[Avg memory] = 

Val(
  ExtractString(
    [Issue].CurrentHierarchyMember.get('Avg  memory data'), '(\d+)', 1)
    )
1 Like
4

Thanks, I updated the missing comma :slight_smile:

5

@janis.plume
I’m looking for advice for performance optimization with that measure. Probably you could help me.

Currently, it takes about 15 sec to build a graph with 6 values when I’m changing page filter values, e.g. “Epic” page filter value. E.g. I have epic ‘A’ and ‘B’, each has 6 values considering all other page filters I apply. So when I change ‘Epic’ filter from A to B or visa versa, it takes 15 secs to build a graph.
I tried page filters and Filter function in measure to limit number of records, but there is no effect on performance, looks like the measure work with all tickets in jira despite on filters. Currently, my jira has about 700k tickets, but for project ‘abc’ it is 10k tickets. But when I add function with additional filter for project value = ‘abc’ performance is not changed:

   MAX(
      Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      [Project].CurrentMember.Name = 'abc'
      AND 
      [Measures].[Issues created]>0),
      [Measures].[Avg memory numeric]
)

Could you please suggest anything to improve performance?

6

Posted new thread for performance issue: Performance issue when calculating Max with filter and descendants