Trying to find Max and Avg for Original Estimated Hours

Questions & Answers
1

I’m using two separate defined measures, but they’re showing the same results.

Average Original Estimate

CASE WHEN
[Measures].[Hours spent] > 0
THEN
Avg(
[Measures].[Original estimated hours]
)
END

Max Original Estimated Hours

CASE WHEN
[Measures].[Hours spent] > 0
THEN
Max(
[Measures].[Original estimated hours]
)
END

But they just show the same numbers.

image
image629×332 8.11 KB

Which isn’t possible, because at least one ticket is greater than that
image

2

Hi @Katie_Petersen,

Thanks for posting your question.

The issue with your formulas is that both Avg() and Max() functions require a set of members to operate on. Without specifying a set, they just return the current value, which is why both measures show the same results.

Make sure that you are importing the Time Tracking measures as they will be needed for the formulas below: Time tracking measures

You will need to create following formulas:

Average Original Estimate

CASE WHEN
  [Measures].[Original estimated hours] > 0
THEN
  [Measures].[Original estimated hours] /
  [Measures].[Issues with Original estimated hours]
END

Max Original Estimated Hours

CASE WHEN
  [Measures].[Original estimated hours] > 0
THEN
Max(
  Filter(
    Filter(
      Descendants(
        [Issue].CurrentMember,
        [Issue].[Issue]
      ),
       IIF([Color].CurrentMember.Level.Name = "Color", 
       [Measures].[Issue color] = [Color].CurrentMember.Name, 1)
      ),
    [Measures].[Issue Original estimated hours] > 0 
  ),
  [Measures].[Original estimated hours]
)
END

A similar approach with the Hours spent, just using “Hours spent” instead of “Original estimated hours” and “Issues with Hours spent” vs. “Issues with Original estimated hours.”

Please note that we are also using a random custom field dimension “Color” that you would need to replace with the dimension you are using for values XS/Small/Medium, etc. The dimension name was not visible from the screenshots you shared in your post.

I hope this helps!

Best,
Marita from support@eazybi.com