Reply
Member
Posts: 50
Registered: ‎04-05-2017
Accepted Solution

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?


Accepted Solutions
Moderator
Posts: 9,146
Registered: ‎03-06-2016

Re: Format negative numbers in red parenthesis

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


All Replies
Member
Posts: 71
Registered: ‎04-11-2017

Re: Format negative numbers in red parenthesis

@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/

Member
Posts: 50
Registered: ‎04-05-2017

Re: Format negative numbers in red parenthesis

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

Moderator
Posts: 9,146
Registered: ‎03-06-2016

Re: Format negative numbers in red parenthesis

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.
Frequent Visitor
Posts: 5
Registered: ‎08-18-2016

Re: Format negative numbers in red parenthesis

TRy,

 

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

Frequent Visitor
Posts: 4
Registered: ‎07-02-2018

Re: Format negative numbers in red parenthesis

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
Highlighted
Member
Posts: 90
Registered: ‎01-26-2016

Re: Format negative numbers in red parenthesis

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