How to show "Average" in the row

Questions & Answers
1

Hi everyone,

I have found this article How to culculate "Average" of rows?

I’m trying to do something very similar - I need to show the average of all rows as it shows up for the Total rows (but I need the average instead of total). I was trying to apply the following:

CASE WHEN
[Issue].CurrentMember.Name="$total_aggregate"
 THEN
  ConstantColumnValue(CatchException(
    Avg(VisibleRowsSet(), [Measures].[OKR Completion %])
  ))
ELSE
CASE
WHEN
Not IsEmpty ([Measures].[Issue Target Metric])
THEN [Measures].[Current Metric Completion %]
WHEN
IsEmpty ([Measures].[Issue Target Metric])
THEN [Measures].[Story Points Completion %]
END
END

But it still calculates the Total instead of the Average. Could you please help me with it? Thanks!

Screenshot 2024-02-26 at 14.17.03
Screenshot 2024-02-26 at 14.17.03892×1206 179 KB

2

Hi @karina.krasik

If you have individual Issue members in the Rows, you can try this formula:

CASE WHEN
  [Issue].CurrentMember.Name="$total_aggregate"
THEN
  [Measures].[Average OKR Completion %]
ELSE
  CASE WHEN
    Not IsEmpty ([Measures].[Issue Target Metric])
  THEN 
    [Measures].[Current Metric Completion %]
  WHEN
    IsEmpty ([Measures].[Issue Target Metric])
  THEN 
    [Measures].[Story Points Completion %]
  END
END

Let me know if this works as expected!
​Best regards,
​Nauris

3

Thanks, Nauris! Since [Average OKR Completion %] is not an actual calculated measure within the report (it’s just calculated in real time as a separate column) we found another solution.

1 Like