Custom formatting shows empty parenthesis

[ykaja]

A measure is defined as difference of two columns.

Custom formatting is defined as #,##;(#,##);"0" . This is mainly to display negative numbers in brackets. For difference of 0, it shows empty brackets(as shown below) for some values instead of 0.

Any thoughts on fixing this please? Thanks

[AlexisOlson · Accepted solution]

Can you try adding ROUND ( ... , 0 ) around the measure you're applying the custom format to? If that fixes the issue, then your problem very likely is that they are small negative values (possibly floating point errors), not actual zeros.

[AlexisOlson]

I think in custom formatting notation, a # drops away if it's zero and I'm guessing the values are small negative numbers that round to zero but aren't actually zero (so the zero formatting doesn't apply).

You could try #,##0;(#,##0);"0", which I'd expect to return (0) instead of () or you could try rounding into the measure.

[ykaja]

@AlexisOlson The values are whole numbers. So the difference should show '0' as both are same numbers.

Already tried this #,##0;(#,##0);"0" and it returns (0) instead of blank parenthesis.

[AlexisOlson · Accepted solution]

Can you try adding ROUND ( ... , 0 ) around the measure you're applying the custom format to? If that fixes the issue, then your problem very likely is that they are small negative values (possibly floating point errors), not actual zeros.

[ykaja · Accepted solution]

Thank you @AlexisOlson. ROUND function fixed this issue.

[AllisonKennedy]

Try: 

#,##;(#,##);0

without the quotes on the zero.

[ykaja]

@AllisonKennedy No change by just removing quotes