Average of all values Except 0

Question

I have a report that I have designed in SSRS 2008. I have a row which has couple of values. This is basically a Survey form where there are Values from 1 to 5 .Some of the values are either Blank or N/A(If they don't answer). But While I calculate Average of the Values it includes that particular value. I think it takes it as 0. So

---

   Average of (4, 5,4,4,5,2,3, ,5) = 3.56 instead of 4.00.

Can You please tell me how I can Calculate Average of the the values without considering Blank values.

Thank you

Answer 1

You can add a calculated field to your dataset to filter out the unwanted values. Then you can just use a regular expression to make it more readable. Here's an example of the calculated field expression:

=iif(Fields!Score.Value > 0, Fields!Score.Value, Nothing)

Now you can simply reference =Avg(Fields!FilteredScore.Value) and it will work as desired.