How do I get the median age for a group of issues?

Hi! As the title suggests, I’m trying to get the median age of a bunch of grouped issues.

I’m using:

Median ( Filter ( {Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]), ChildrenSet([Issue].CurrentHierarchyMember)}, [Measures].[Days Open] > 0 ), [Measures].[Days Open] )

And [Measures].[Days Open] is:
DateDiffDays([Measures].[Issue created date], Now())

The result I’m getting, however, is the same number of all groups of categories:
image

If I drill through, each individual issue does display its distinct age.
Thanks in advance!

Try This…

CASE WHEN [Measures].[Issues resolved] > 0 THEN
[Measures].[Total resolution days] / [Measures].[Issues resolved]
END

Hey @kuldeep.u ,

Thanks for the suggestion, I’m afraid this doesn’t work for a couple of reasons:

  • It doesn’t drill into the individual issues from each group
  • I believe the formula is for an average (i.e. dividing the two) as opposed to a median value?

You are right, thanks…

1 suggestion instead of DateDiffDays, try to use DateDiffWorkDays that will give you information based on Working days. (You can choose as per requirement)

Secondly, Instead of Issues in Row Dimensions, you may use EPIC or Specific Label or other fields to group issues then only you will get Median based on group.

1 suggestion instead of DateDiffDays, try to use DateDiffWorkDays

Thanks for the tip, I don’t mind either way which ones we count, as long as I can get the median value.

Secondly, Instead of Issues in Row Dimensions, you may use EPIC or Specific Label or other fields to group issues then only you will get Median based on group.

I am already using groups of issues and not individual issues in the Row dimension, my issue is that I am not getting the median value per group (just the same large value, feels like it’s pulling the age of all issues in the project, and not just the ones in the group).

Hi @Vlad_Neykov

Welcome to the Community! :slight_smile:

Your formula contains issue properties, but it doesn’t contain a measure. It is important to use at least one measure or fixed value when defining new measures, as it will respond to your report context and will display the necessary values according to connected Dimensions.
Measure is a treasure!

In this case you could use the following formula for the Median calculation:

Median(
  Filter(
    DescendantsSet([Issue].CurrentMember, [Issue].[Issue]),
    [Measures].[Issues created]>0
  ),
  [Measures].[Days Open]
)

This formula contains the Issues created measure as a filter and thus gives this measure a way how to follow the report context. Now the formula should respond to different Dimension selections and calculate the data accordingly.

You can check out this video on the differences between measures and properties: Training videos on specific topics (starting from 9:30)

Best regards,
Nauris / eazyBI support

1 Like

Thanks Nauris!
This works like a charm.

I’ll check out the links you provided to learn more about it :slight_smile:

1 Like