SSRS Avg function returns result that is different than expected

average, function, iif-function, reporting-services, ssrs-2008

Solution

You can definitely use `IIf` within `Avg` and get the correct results.

Do you want to exclude the `False` values from the calculation entirely?

In your example you're still including them, just setting them to `0` and hence still including them in the calculation. This might explain your unexpected results.

If you want to exclude them entirely use `Nothing` instead of `0`.

Edit after comment

You can nest an expression in another `IIf` statement and check for NULL values using `IsNothing`.

Say your condition average expression is:

=Avg(IIf(Fields!ID.Value > 5, Fields!value.Value, Nothing))

You can return 0 for NULL values with something like:

=IIf(IsNothing(Avg(IIf(Fields!ID.Value > 5, Fields!value.Value, Nothing)))
    , 0.0
    , Avg(IIf(Fields!ID.Value > 5, Fields!value.Value, Nothing)))

Problem

Currently I am trying to average a set of numbers, but with certain conditions. Is it possible to use an `iif()` within an `avg()` and return the correct result? Furthermore, as of now my computations return a decimal returned to a power (8.9267....E -05). I tried circumventing the `AVG` function by conditionally summing and then dividing by a conditional count but it gives me the same results. Can someone explain why this is returned and offer help? Currently I have: ``` =avg(iif((This_case) AND (That_case) AND (This_conditional) , Fields!ResponseRate.Value , 0)) ``` Essentially I want the average ResponseRate if certain conditions are met. The `sum` function works fine for the conditions but the average doesn't.

Original source