SSRS formula or expression to change NaN to 0

Question

I use the following expression to calculate the percentage:

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

Days.Value shows up as 0, but in a few of my results, instead of reading 0% in my percentage column, it actually reads NaN (Not a Number).

Does anyone know the exact forumla expression that I need, and where should I insert it into the current expression to say: "Where is the NaN shown, put" 0 "instead?

(See image)

Answer 1

What about

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

Answer 2

I was not lucky with the above answers. Here is 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")) 

Answer 3

I used this for a similar occasion,

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

Answer 4

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

Answer 5

This is the easiest and 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 0 instead of nothing.

Answer 6

Here is another option. It should solve the problem, as well as get rid of the Infinite answers:

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

Answer 7

I had a similar problem with this and it turned out 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 it works.