Forum Discussion
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
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.
That helps a lot!!!!
Thanks SteelBreeze!