cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Len
Regular Visitor

How to convert negative numbers into positive numbers

Hi all,

 

Apologies in advance if this has been answered before.

 

As the subject suggests; How can I convert negative numbers into positive numbers?

 

Thanks

4 REPLIES 4
mchacon
Frequent Visitor

Calculate column, from negative to positive and keep positive as positive.

= each if [COLUMN] < 0 then [COLUMN] *-1 else [COLUMN] *1)

lescamp79
Advocate I
Advocate I

You could also highlight the column in the query editor and right click, select transform, and choose absolute value. That would give the positve number outcome you're looking for. 

Greg_Deckler
Super User
Super User

@Len - You might have to wrap it in an IF statement if you want to preserve already positive numbers. 

 

You could also use the ABS function (absolute value)

 

You cold also multiple by -1, "[Column] * -1"

 

With ABS you could get rid of negative numbers while not impacting the positive numbers.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

KHorseman
Community Champion
Community Champion

Just put a "-" or a "0 -" in front. A double negative is a positive.

 

For instance, I have a measure that calculates lost opportunities, intended to be used on a column chart along with a net gain measure. What I want is for the loss to be a descending column below the 0 line, and the gain to be a positive column above. The basic calculation of each is a sum, so they normally both return a positive number. So my formula instead is:

 

Lost Opportunity = 0 - CALCULATE(

SUM(Items[Forecast Amount]) - SUM(Items[Actual]),

FILTER(

Items,

Items[Status] = "Cancelled"

)

)

 

Elsewhere I have another measure that requires the positive number version of Lost Opportunity, so:

 

% Lost = DIVIDE(-[Lost Opportunity], [Total Gain])

 

Lost Opportunity already returns a negative number, so -[Lost Opportunity] converts it back.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors