SSRS Avg function returns result that is different than expected

Question

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.

Answer 1

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)))