Computing Average in Reporting Services exclude values less than 0

Question

Hello - I have this for a textbox in a SQL Server Reporting Services report. This is to take the total average

=(Sum(iif(Fields!Q1Rating.Value>0,Fields!Q1Rating.Value,0))/(Count(Fields!Q1Rating.Value) - Sum(iif(Fields!Q1Rating.Value<0,1,0))) +                     
Sum(iif(Fields!Q2Rating.Value>0,Fields!Q2Rating.Value,0))/(Count(Fields!Q2Rating.Value)) - Sum(iif(Fields!Q2Rating.Value<0,1,0)))+                    
Sum(iif(Fields!Q3Rating.Value>0,Fields!Q3Rating.Value,0))/(Count(Fields!Q3Rating.Value) - Sum(iif(Fields!Q3Rating.Value<0,1,0))) +                    
Sum(iif(Fields!Q4Rating.Value>0,Fields!Q4Rating.Value,0))/(Count(Fields!Q4Rating.Value) - Sum(iif(Fields!Q4Rating.Value<0,1,0)))/4

I don't want to divide by 0 because if the value for Q3Rating is -1 then it will be displayed as 0 and I want to exclude those values. It would be -1 if someone choose N/A and I am representing N/A as -1.

Answer 1

So, If you do not want to use values 0 or less, why not just exclude them as your first if does?

Explain more about what you are after and you will get a response.

You can always set it to 1. This way, you will avoid divide by zero errors and just come out with the original value.

=(Sum(iif(Fields!Q1Rating.Value>0,Fields!Q1Rating.Value,1))/(Count(Fields!Q1Rating.Value)            
 - Sum(iif(Fields!Q1Rating.Value<0,1,1))) + Sum(iif(Fields!Q2Rating.Value>0,Fields!Q2Rating.Value,1))/(Count(Fields!Q2Rating.Value)) - Sum(iif(Fields!Q2Rating.Value<0,1,1)))            
+Sum(iif(Fields!Q3Rating.Value>0,Fields!Q3Rating.Value,1))/(Count(Fields!Q3Rating.Value) -             
Sum(iif(Fields!Q3Rating.Value<0,1,1))) +Sum(iif(Fields!Q4Rating.Value>0,Fields!            
Q4Rating.Value,1))/(Count(Fields!Q4Rating.Value) - Sum(iif(Fields!Q4Rating.Value<0,1,1)))/4

Computing Average in Reporting Services exclude values less than 0

Follow this question

Related Questions