Average line on calculated field is not taking into account the ones with zero

Average line on calculated field is not taking into account the ones with zero.

(the green bars)
The field ‘Auto Done’ definition is:
( [Measures].[Transitions to issues count],
[Transition Field].[Automation],
[Automation].[Done] )

Hi @Jacob.Weiss,

It seems you are using the eazyBI standard calculation to return the average - Create reports.
The calculation would consider the value of zero if it would be present. In your report, the value doesn’t exist, which is why the calculation ignores it. You can see that in the standard calculation formula when you click on it and choose “Edit formula”. Please see the picture and an example of the formula below:

CASE WHEN NOT IsEmpty([Measures].[Issues created]) THEN
  Avg(VisibleRowsSet(), [Measures].[Issues created])

The NOT IsEmpty([Measures].[Issues created]) condition does that. So, you have an option to copy the formula and remove the condition. The formula then would look similar to the one below:

  Avg(VisibleRowsSet(), [Measures].[Issues created])

The calculation would still not consider the cases it is empty but will display the calculated value for the members.

There is another option to replace empty values with zero, but that would impact the returned result. You can define a new calculated measure and replace an empty value with a different value with the CoalesceEmpty() function - CoalesceEmpty.

See the difference in results below:

Roberts // support@eazybi.com

Thanks Roberts,
I used the CoalesceEmpty() and it works well :slight_smile: