Forum Discussion
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?
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
15 Replies
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:
- 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)") - 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.
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
- Create a new measure to add the brackets for the negative numbers: For example,
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 YuTRy,
= Format([Measure],"#,##.#0;(#,##.#0)")
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
Is there a solution that doesn't turn the values into Text? When I sort Ascending or Descending the values line up incorrectly when we get into the negatives.
example descending: 2.0, 0, (1.8), (0.3) when it should be sorting like this 2.0, 0, (0.3), (1.8)
But if the format was some sort of number it will sort correctly.
Hi Tmendoza
The Sep 2019 release of Power BI has finally made custom number formatting available, which makes this issue much easier. You can now apply formatting strings to columns and measures, like in Excel. See https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-september-2019-feature-summary/ and search for "custom format strings". If you're not familiar with format strings, read the links provided in the page linked to above, and/or read some pages on Excel format strings to understand how they work. The only counter-intuitive feature in PBI is that you must define the formats in the Model view.
The trick is that you can define a string for positive numbers, one for negative numbers and one for zero, separated by semicolons. There are, however, two features that work in Excel that don't work in Power BI (as far as I can tell), which make things a bit harder than I'd like.
First, in Excel, you can precede a formatting string with a color name in square brackets to make those numbers display in that color, for example #,##0;[Red](#,##0);0, would make negative numbers show in brackets in red with no decimals and comma thousands separators. This is a simple fix for the issue, but not in PBI, alas.
Second, if the numbers are right aligned, the negative numbers will sit further to the left than the positive numbers due to their ending bracket. In Excel, you can make the positive and negative numbers line up by padding the positive string to the left by the width of a bracket, by putting _) at the end of the string, for example #,##0_);(#,##0);0. Again, not in PBI.
The first issue above can be worked around in PBI by using an "uncolored" format string (for example #,##0.00;(#,##0.00);0.00 to show all numbers with two decimals and comma thousands separators) and then applying a conditional format to the numbers to change the font color to red if they are less than zero (as I explained previously.
I can't see a workaround for the second issue at present, so the positive and negative values will be slightly misaligned.
Hope that helps.
Hi
,
Formatting negative numbers in parentheses is great to differentiate positive and negative in for example financial reporting, so good question. And it used to be quite difficult in Power BI before. You would have to use the FORMAT function. Not only would this change the formatting, but also turn a measure into text. So aggregation would be difficult. But today you can simply use custom formatting for a measure. Try custom formatting: "#,##0.0, (#.##0.0)". You can find more details on this here:
Display Negative Numbers between Parentheses
Keep crushing it!
Rick
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
More up votes (#,#;[Red](#,#);"-" add color to custom formula)
https://ideas.powerbi.com/ideas/idea/?ideaid=c08e3e42-2b77-4cae-9fd9-a51c599e2cbb
https://ideas.powerbi.com/ideas/idea/?ideaid=c26285eb-e1ed-4b7d-986c-2bcb7cc351d9
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)
Check out this link, if you go to control panel and change the negative numbers to brackets, it should work.
I'm having an issue, but that because it's euro I'm working with
