Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Whitney
Helper II
Helper II

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
Anonymous
Not applicable

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

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.
 samplesample
 
 

View solution in original post

5 REPLIES 5
slanka
Helper I
Helper I

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.
 samplesample
 
 

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

Anonymous
Not applicable

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

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.