Complex SSRS expression (Average of Average)

Question

I have 4 datasets and I need to calculate the average of a field and their cumulative average.

Here are my 4 datasets : Dataset1,Dataset2,Dataset3,Dataset4:

This what I want . I want to find the average of the average values as given below :

Avg(Fields!Discount.Value,"Dataset1")
Avg(Fields!Discount.Value,"Dataset2")
Avg(Fields!Discount.Value,"Dataset3")
Avg(Fields!Discount.Value,"Dataset4")

A logic of = Avg(Avg,Avg,Avg..) throws an error. So basically it doesn't work. There's gotta be a way surely ?

We need to also take into account that sometimes one of the datasets may be empty ( null or 0 ). Is there any way of doing it in SSRS ?

AVG only works in a dataset field or returned values by operations on it. If your dataset number is static you can use the average formula to calculate the global average `(avg1+avg2+avg3+avg4)/4`. Let me know if you need further help. — alejandro zuleta, Mar 21 '16 at 21:16

score 0 · Answer 1 · answered Mar 21 '16 at 22:33

If you don't need a weighted average you can use ISNOTHING to check for the NULLs like:

=(IIF(ISNOTHING(Avg(Fields!Discount.Value,"Dataset1")), 0, Avg(Fields!Discount.Value,"Dataset1") ) + 
  IIF(ISNOTHING(Avg(Fields!Discount.Value,"Dataset2")), 0, Avg(Fields!Discount.Value,"Dataset2") ) + 
  IIF(ISNOTHING(Avg(Fields!Discount.Value,"Dataset3")), 0, Avg(Fields!Discount.Value,"Dataset3") ) + 
  IIF(ISNOTHING(Avg(Fields!Discount.Value,"Dataset4")), 0, Avg(Fields!Discount.Value,"Dataset4") ) ) / 4

Complex SSRS expression (Average of Average)

1 Answers1