How do I use IIF to Avg

Hi experts Max Points.. How do I change this expression so that it will not include the fields with Zero in  the averaging...
=Avg(CDec(IIF(IsNumeric(Trim(Replace(Replace(Fields!ShipmentPercentage.Value,"NaN",""),"%",""))), Trim(Replace(Replace(Fields!ShipmentPercentage.Value,"NaN",""),"%","")) ,  0.0 ) ))

Open in new window

LVL 1
SPLadyAsked:
Who is Participating?
 
itcoupleCommented:
Hi

Way to long expression for my to work out ;)

See below sample which should work.

=AVG(IIF(Fields!Field.value=0,nothing,Fields!Field.value)

I suggest to put your expression into calculated field in dataset (without AVG) just so you can refer to calculated field without building very very long expression in one place.


Hope that helps
Emil
0
 
SPLadyAuthor Commented:
Thank you Emil! Excellent :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.