Format String Issues with Negative Values

[delish_hins]

I created a related post earlier today but I wanted to break this issue out separately. I am using the format string "#,#,;(#,#);" to display my values in thousands. The issue I have is if the value is between -1 and -999 then the format string returns empty parens "()" which looks silly but is also confused for 0/zero, which further leads to inconsistencies in formatting since zero is displayed as blank.

Is there a way to suppress these empty parens?

[suparnababu8]

Hi @delish_hins 

On top of soultion provided by Greg Deckler, you give a try solve your issue. Create a calculated column that formats the values based on your condition. 

Salesformattedvalu = 
IF(
    [Sales] < -1 && [Sales] > -999,
    "(" & FORMAT([Sales], "#,#") & ")",
    FORMAT([Sales], "#,#;(#,#);0;"))

Let me know if it works!

Thanks!

[delish_hins]

Thanks. Something similar to this is where I landed for my original issue but I still have some empty parens even though my measure is supposed to return blank when when the value is between -999 and -1.

[Greg_Deckler]

@delish_hins I suppose you could conditionally format that if the value is between -1 and -999 that the font color is white.

[delish_hins]

I'm doing something similar in other cases but in this particular matrix I'm using a single measure and a calculation group that is returning both $ and % and the conditional formatting functionality doesn't appear to be capable of handling both unfortunately.