cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Format negative numbers in red parenthesis

Good Morning, I have a table I have uploaded to PBI and when creating a table/matrix I would prefer to have negative values viewed in red parenthesis. Is this option available?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @Miskondukt,

 

Currently, there is no OOTB option for us to format the negative number as (value) and keep the number data type at the same time. We can only create a calculated column below to return (value) format but the result will be treated as TEXT instead of number, and Conditional Formatting is not available.

 

Column = IF('Table2'[Column1]<0,"(" & FORMAT('Table2'[Column1],"General Number") & ")" ,FORMAT('Table2'[Column1],"General Number"))

 

For your requirement, you can submit a idea here.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

14 REPLIES 14
Resolver I
Resolver I

@Miskondukt

 

Is this what you are after?  It appears you can conditionally format your matrix via negative numbers to any color with the March version of Power BI.  

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-conditional-table-formatting/

To a degree, that works; however, it is simply a color formatting based on values shaded on that specific value whereas I am trying to get to any number less than 0 is (red)

 

PBIValues4.pngPBIValues5.png

Community Support
Community Support

Hi @Miskondukt,

 

Currently, there is no OOTB option for us to format the negative number as (value) and keep the number data type at the same time. We can only create a calculated column below to return (value) format but the result will be treated as TEXT instead of number, and Conditional Formatting is not available.

 

Column = IF('Table2'[Column1]<0,"(" & FORMAT('Table2'[Column1],"General Number") & ")" ,FORMAT('Table2'[Column1],"General Number"))

 

For your requirement, you can submit a idea here.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

TRy,

 

= Format([Measure],"#,##.#0;(#,##.#0)")

Advocate I
Advocate I

You can use the DAX Format function to format negative numbers with enclosing brackets. However, the result is a text value. You can use conditional formatting to make negative numbers show as red (but not text values). So…

 

If you can live with a negative sign and red, it’s easy, just use the conditional formatting (see screen shot below).

 

If you must have brackets and red, my somewhat involved solution:

 

  1. Create a new measure to add the brackets for the negative numbers:  For example,

    Total Including Tax Formatted = FORMAT([Sum of Total Incl Tax],"#,##.#0;(#,##.#0)")

  2. Apply the red-if-less-than-zero conditional formatting to the formatted measure. Because the result of the Format function is text, you can’t use the value of the formatted measure to determine if it’s less than zero. Instead, use the value of the original measure (Sum of Total Incl Tax in the screen shot below) to add the red to the formatted measure when negative. You don’t need to include the original measure in the visual, but it must be in the table. Also, being text, the formatted measure aligns to the left, and I can’t see how that can be changed.


  3. image.png

Hi,

 

I was following your example and it did work. Thank you!

One question though, looking at your screenshot, it looks as if you were able to summarize the formatted column although it is TEXT and you also mentioned that in your instruction.

How were you able to do a summation on a TEXT format column.

 

Thanks!

NH

 

I tried this but in all of my other columns I am showing data in Millions. And I'm also showing it with no decimal places. Is there a way to use this to remove the decimal places and to show an M after the value because the format field option is no longer available for this field once I use the format function.

 

Also, it's still showing the - symbol inside of the parens. I just want the parens.

I haven't tested this, but PBI formatting works similarly to Excel and C# formatting. So, you should be able to add two commas after the string to divide the number by a million, then add "m" or " m" (if you want a space before the m) after the number. Try something like this: #,##0,," m";(#,##0),," m";0

 

See these links for more detail on Excel and c# formatitng strings. The comma section in the Excel link explains the divide by a million trick I used above:

 

https://exceljet.net/custom-number-formats

https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings

thank you!! 

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors