cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mloc1990 Frequent Visitor
Frequent Visitor

% difference between 2 negative numbers is a positive when it should be negative

Hi,

 

I'm hoping someone can help me. I have the below calculated column that compares the current field X to the same field 30 days ago Y to get a % difference

 

% diff 30 days ago = iferror(table[X]/table[Y],0)
 
The issue I'm having is if the current field of X is a bigger negative number than Y it gives me a positive value of % change instead of  negative.
 
e.g X -48613 and Y  -13694 it gives a positive % difference when it should be negative. Is there a way to correct this?
 
Thanks
1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: % difference between 2 negative numbers is a positive when it should be negative

Hi @mloc1990 ,

 

 

% diff 30 days ago =IF(X<0 && Y<0 && X<Y,-1* iferror(table[X]/table[Y],0),iferror(table[X]/table[Y],0))

 

 

I would also use DIVIDE(), but basically wrap in an IF().  
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

View solution in original post

2 REPLIES 2
Nathaniel_C Super Contributor
Super Contributor

Re: % difference between 2 negative numbers is a positive when it should be negative

Hi @mloc1990 ,

 

 

% diff 30 days ago =IF(X<0 && Y<0 && X<Y,-1* iferror(table[X]/table[Y],0),iferror(table[X]/table[Y],0))

 

 

I would also use DIVIDE(), but basically wrap in an IF().  
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

View solution in original post

Highlighted
mloc1990 Frequent Visitor
Frequent Visitor

Re: % difference between 2 negative numbers is a positive when it should be negative

Hi @Nathaniel_C 

 

This works great thanks for your help! I changed my formula a bit to the below and it appears to be doing what I need it too.

 

% diff 30 days ago = IF(X<0 && Y<0 && X<Y,-1* iferror(X/Y,0)+1,iferror(X/Y,0)-1)
 
Thanks!
 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,960)