SSRS Formula or expression to change NaN to 0

Question

I am using the following expression to work out a percentage:

=Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name")

Days.Value is showing as 0 however in a few of my results instead of reading 0% in my percentage column it is actually reading NaN (Not a Number).

Does anyone know the exact expression forumla i need and where I should paste it in my current expression to say "Where NaN is showing, put a '0' instead?"

(See image)

Bradytelic

Answer 1

How about

=IIF(Fields!Days.Value > 0,Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name"),0)

Rill

I tried something similar to this: =IIF(Sum(Fields!TotalPrice.Value)/Sum(Fields!Orders.Value) = 0, 0, Sum(Fields!TotalPrice.Value)/Sum(Fields!Orders.Value) netted me some weird results in a text box I deemed as currency. The answer above worked perfectly. Thanks! – Disoperation

This solution still gave me the same error. I've answered this question with what worked for me. – Predicate

Answer 2

I didn't have luck with the above answers. Here's what worked for me:

=IIF(Single.IsNAN(Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name")), 0, Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name"))

Predicate

Answer 3

I used this for similar case,

=REPLACE(Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name"),"NaN","0")

Onstad

Answer 4

Here's another option. It should solve the problem, and also get rid of Infinite responses:

=val(replace(Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name"),"NaN","0"))

Dilley

Answer 5

Try

=IIf(Fields!Days.Value Is Nothing Or Sum(Fields!Days.Value, "Date_month_name") Is Nothing, 0, Fields!Days.Value / Sum(Fields!Days.Value, "Date_month_name"))

Contemplation

Answer 6

This is the simplest & best, I think,

=Switch(
Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name") = "NaN",Nothing,
Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name") = "Infinity",Nothing,
Fields!Days.Value/Sum(Fields!Days.Value, "Date_month_name") = "-Infinity",Nothing
)

You can also put a 0 instead of nothing.

Culminant

Answer 7

I had a similar issue to this and found that the following was easiest to do.

=Iif(
Fields!Days.Value.Value <> 0 AND Sum(Fields!Days.Value, "Date_month_name") <> 0
, Fields!Days.Value.Value/Sum(Fields!Days.Value, "Date_month_name")
, 0
)

Probably not the best solution, but works.

Romo