Forum Discussion
Display Negative Numbers in Parenthesis
I am using a SWITCH function to format a measure. How to display in parenthesis with no rounding. Please help me adjust my switch function.
These negative number should be displayed like (36k), (.999k), (14k)
My SWITCH function:
.formattedMeasure =
var _salesMeasure =
[total_sales]
var _result =
SWITCH(
TRUE(),
ABS(_salesMeasure) > 1000000000, "$"&FORMAT(_salesMeasure, "#,,,.0B"),
ABS(_salesMeasure) > 1000000, "$"&FORMAT(_salesMeasure, "#,,.0M"),
ABS(_salesMeasure) > 1000, "$."&FORMAT(_salesMeasure,"#,K"),
ABS(_salesMeasure) > 0, "$"&FORMAT(_salesMeasure,"#")
)
RETURN
_result
Anonymous
This is the default currency format string:
"\$#,0.###############;(\$#,0.###############);\$#,0.###############"So try:SWITCH(
TRUE(),
ABS(_salesMeasure) > 1000000000, FORMAT(_salesMeasure, "\$#,,,.0B;(\$#,,,.0B);\$#,,,.0B"),
ABS(_salesMeasure) > 1000000, FORMAT(_salesMeasure, "\$#,,.0M;(\$#,,.0M);\$#,,.0M"),
ABS(_salesMeasure) > 1000, FORMAT(_salesMeasure,"\$#,K";(\$#,K);\$#,K""),
ABS(_salesMeasure) > 0, FORMAT(_salesMeasure,"\$#;(\$#);\$#")
)
2 Replies
Anonymous
This is the default currency format string:
"\$#,0.###############;(\$#,0.###############);\$#,0.###############"So try:SWITCH(
TRUE(),
ABS(_salesMeasure) > 1000000000, FORMAT(_salesMeasure, "\$#,,,.0B;(\$#,,,.0B);\$#,,,.0B"),
ABS(_salesMeasure) > 1000000, FORMAT(_salesMeasure, "\$#,,.0M;(\$#,,.0M);\$#,,.0M"),
ABS(_salesMeasure) > 1000, FORMAT(_salesMeasure,"\$#,K";(\$#,K);\$#,K""),
ABS(_salesMeasure) > 0, FORMAT(_salesMeasure,"\$#;(\$#);\$#")
)