Forum Discussion

Miskondukt's avatar
Miskondukt
Helper III
9 years ago
Solved

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?
Tips and Tricks
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    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

SteelBreeze's avatar
SteelBreeze
Advocate I
7 years ago

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

joann71's avatar
joann71
Regular Visitor
7 years ago

thank you!!