cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Whitney
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
JoseCruzCat Helper I
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!! 

View solution in original post

slanka Helper I
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.
 image.pngsample
 
 

View solution in original post

5 REPLIES 5
JoseCruzCat Helper I
Helper I

Re: Value furthest from zero

If You use min(table[columnName]) or max(table[columnName]) solve your problem??
Whitney
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
JoseCruzCat Helper I
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!! 

View solution in original post

slanka Helper I
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.
 image.pngsample
 
 

View solution in original post

Whitney
Frequent Visitor

Re: Value furthest from zero

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

Helpful resources

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

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

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors