How to fix "Infinity" and "Nan"

Question

I am getting "Infinity" or a "NaN" value on my expression below. Suggestions on my expression to improve it?

=Sum(CDbl(IIF(Fields!Type.Value = "Charges", Fields!PatientAmount.Value + Fields!InsuranceAmount.Value, 0))) /(CountDistinct(iif(Fields!IsEncounter.Value = 1, Fields!PatientVisitId2.Value,Nothing)) - COUNTDISTINCT (IIF(Fields!IsEncounter.Value = -1, Fields!PatientVisitId2.Value,Nothing)))

Answer 1

Hi,

In the denominator "CountDistinct(iif(Fields!IsEncounter.Value = 1, Fields!PatientVisitId2.Value,Nothing))"

In this expression, if the Fields!IsEncounter.Value≠1, it will be CountDistinct(nothing)=0, zero cannot be used as a divisor. So there will be "Infinity" and "Nan".

I assumed that you're trying to do calculations on the entire data.

So you should use the expression on a single textbox instead of a column.

If I misunderstand, please check the needs you want and then we can improve it.

If you have any question, please feel free to let me know.

If your problem has been solved, please mark "accepted answer" on my reply, thank you for your understanding.

Regards,

Zoe