How to culculate "Average" of rows?

Questions & Answers
1

Greetings!
Could anyone help me to culculate Average value of columns?
There is a build in method: Add calculated → statistical → Average. But the result looks like this(vertical column with identical values):

image
image1372×785 58.7 KB

Is it possible to get “Average” row like “Total” row?
I’ve spent a lot of time but still can’t figure out how to do it.
Thanks a lot!

2

Hi @popovsa88

When you add the Add calculated → Statistical → Average column, you can click on the column header and choose the “Edit calculated” option to see and copy the formula used in this calculation.

It should look something like this:

CASE WHEN NOT IsEmpty([Measures].[Diff%]) THEN
  ConstantColumnValue(CatchException(
    Avg(VisibleRowsSet(), [Measures].[Diff%])
  ))
END

Now, edit the formula of your “Diff%” measure to override the calculation of the Total row:

CASE WHEN
[Team].CurrentMember.Name="$total_aggregate"
THEN
ConstantColumnValue(CatchException(
    Avg(VisibleRowsSet(), [Measures].[Diff%])
))
ELSE
-- your Diff% formula goes here
END

Let me know if this fits your use case!
Best regards,
Nauris / eazyBI support

3

Thanks a lot! That works!

4

This solution works great for changing the value of the Total row to the average of column instead of the sum. Is there any way to change the row label from “Total” to “Average”?

5

Hi @wnoble

Unfortunately, it is not possible to change the names of the Total rows/columns.

​Best regards,
​Nauris

6

That’s what I thought, but I’ve been surprised by eazyBI’s capabilities in the past :slight_smile:.

1 Like