Forum Discussion
Whitney
Helper II
Helper IIValue 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 r...
Yes!!... You can use this as a measure
Furthest = if(ABS(MAX(Hoja1[column]))>ABS(MIN(Hoja1[column]));MAX(Hoja1[column]);MIN(Hoja1[column]))Regards!!
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
WhitneyHelper II
Helper IIHi 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.
Yes!!... You can use this as a measure
Furthest = if(ABS(MAX(Hoja1[column]))>ABS(MIN(Hoja1[column]));MAX(Hoja1[column]);MIN(Hoja1[column]))
Regards!!