Forum Discussion
Value furthest from zero
Hi,
I have data that captures the best (as a postive number e.g. 3) and worst (as a negative number e.g. -5) position for a workshop in any given fortnight.
I'm trying to write a formula to return the value in any respective fortnight that is furthest from zero i.e. the largest absolute value; if the value is negative, I need the negative value returned not the absolute value.
I'm assuming it will use the absolute function however, I can't figure out how to return the negative value if it is further from zero than the positive number.
Any help is greatly appreciated!
Hey there,
To achieve this, you need to write couple of calculated columns,
1) To calculate the ABS value,
absolute = IF('Table'[Position] <0, 'Table'[Position] * -1, 'Table'[Position])2) To calculate the max in the set along (along with it's negative sign, if it's negative),Max =VAR calc = CALCULATE(MAX('Table'[absolute]), FILTER('Table','Table'[absolute]))Return If(calc = 'Table'[absolute],'Table'[Position])I've created a sample report. Please find the attached. Let me know if you need more help.sample
5 Replies
Hey there,
To achieve this, you need to write couple of calculated columns,
1) To calculate the ABS value,
absolute = IF('Table'[Position] <0, 'Table'[Position] * -1, 'Table'[Position])2) To calculate the max in the set along (along with it's negative sign, if it's negative),Max =VAR calc = CALCULATE(MAX('Table'[absolute]), FILTER('Table','Table'[absolute]))Return If(calc = 'Table'[absolute],'Table'[Position])I've created a sample report. Please find the attached. Let me know if you need more help.sampleHi Anonymous,
Using min() or max() neglects the other side of zero. I.e. max takes the largest positive number and min takes the largest negative number, and I am looking for the value furthest from zero which could be positive or negative. Unless there is a way to use min() and max() together in a function to return the value I'm looking for? I'm not sure.
