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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

9 REPLIES 9
KaviKumaran
New Member

Hi Guys,

I saw the Answer on how to convert negative to positive values and I did it ! Now I have new column with positive values ( which are Ex negative values)

Now I try to use formula SUM to sum up the values, no error in measure. But when I put the measure in Visual, it shows blank. 

I already corrected the Data type: Decimal Number (which is true) and Format: Currency, and Summarization: SUM

Nothing helps. Please help!

AliceW
Impactful Individual
Impactful Individual

This is strange. Have you thought about going into the 'Table View' of your report and looking at the values that are actually in the new column? If they are blanks in there too, your formula is wrong somehow.

If they're NOT blanks, the visual might be influenced by page filters (I've been here too many times :)). Try opening a new page without any filters and adding the measure there in a new visual. 

Hope this helps!

yes you are correct @AliceW  .. Thanks for the Tip>

It is just a date filter issue. I never realised that until I compare the row count with Excel file. Sometimes we have to go outside the box to fix things! thanks 

AliceW
Impactful Individual
Impactful Individual

My pleasure, @KaviKumaran ! Keep on writing DAX 🙂

mchacon
Advocate I
Advocate I

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

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

AliceW
Impactful Individual
Impactful Individual

Thank you for your solution! I'd also add that it also works for measures.

 

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors