Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How can I make average (avg) function to ignore NULL, BLANK and hyphen ( - ) fields? I only want average to take into account valid numeric values. At the same time, I want to make the average of certain fields complaining a criteria (Order Type='Pending').
=avg({$<[Order Type] = {"Pending'}>} if(isNumeric([Hours]), [Hours])))
it will be equivalent to:
=sum({$<[Order Type] = {'Pending'}>}[Hours])/count({$<[Order Type] = {'Pending'}>}if(IsNum([Hours]),[Hours]))
Is it correct?
do you try this?
=avg({$<[Order Type] = {"Pending'}>} [Hours])
Does avg function ignore by default Null, blanks and hyphen fields as sum function does? I mean, I suppose avg function is not taken into account the nulls, blanks and hyphen fields (non-numeric) when performing calculations. Am I right?
yes avg ignore those troublemakers by default.
the avg function ignores everything that is not number