cancel
Showing results for
Did you mean:
Frequent Visitor

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Helper I

## Re: Value furthest from zero

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

Helper I

## Re: Value furthest from zero

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 5
Helper I

## Re: Value furthest from zero

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

## Re: Value furthest from zero

Hi @JoseCruzCat,

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.

Highlighted
Helper I

## Re: Value furthest from zero

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

Helper I

## Re: Value furthest from zero

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

Frequent Visitor

## Re: Value furthest from zero

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

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors