Value furthest from zero

[Whitney]

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!

[Anonymous · Accepted solution]

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!! 

[slanka · Accepted solution]

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

[Whitney]

@slanka @Anonymous thank you for your help, both methods work great!

[Anonymous]

If You use min(table[columnName]) or max(table[columnName]) solve your problem??

[Whitney]

Hi @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.

[Anonymous · Accepted solution]

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!!