Excluding from a column values higher than 1000 and lower than -1000 using IF

[pierpa]

Hello everyone, I have a little problem that I can't solve.

I have a table in which I calculate the delta between two values. This delta is shown as a percentage. Sometimes I have values higher than 1000 and lower than -1000, I would like to exclude them from the column.

Now i calculate the delta with a measure called Var2%, then I created a new measure called Var% Adj to filter the data.

I tried with nested IF

Var% ADJ =
IF ( [Var2%] > 1000; "ND"; IF ( [Var2%] <= -1000; "ND"; [Var2%] ) )

While it successfully discards values higher than 1000, the measure doesn't discard values lower than -1000

Var2%	Var% ADJ
20444050003040530,0%	ND
0,0%	0,0%
24,9%	24,9%
-4065,7%	-4065,7%

Any suggestion? Thank you

[vik0810 · Accepted solution]

Hi @pierpa,

your measure Var2% is a percentage. That's why you must use 10 instead of 1000 in var% ADJ (1000% = 10).

Var% ADJ =
IF ( [Var2%] > 10; "ND"; IF ( [Var2%] <= -10; "ND"; [Var2%] ) )

[pierpa]

Thank You!!!

I feel really dumb, I was thinking about the original value. Thank you again!