cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hymieho Regular Visitor
Regular Visitor

Custom FORMAT to add brackets/parentheses to negative numbers ALSO shows pre-pended hyphen/dash

Below is my DAX for adding handling the formatting of numbers that will be displayed in cards. It works great, however negative numbers are being shown as -($935). Where is the hyphen/dash coming from & how do I suppress it?

 

Thanks!

 

RM (LCUR) Display = 
VAR
Total=
IF(
HASONEVALUE('Booked Order'[Region]),
SWITCH(VALUES('Booked Order'[Region]),
"NA", IF(ABS(SUM('Booked Order'[RM (LCUR)])) > 999999, FORMAT(SUM('Booked Order'[RM (LCUR)]),"$#,##0,,.00M;($#,##0,,.00M)"),
IF(ABS(SUM('Booked Order'[RM (LCUR)])) > 999, FORMAT(SUM('Booked Order'[RM (LCUR)]),"$#,##0,K;($#,##0,K)"),
FORMAT(SUM('Booked Order'[RM (LCUR)]), FORMAT(SUM('Booked Order'[RM (LCUR)]),"$#,##0;($#,##0)")
))),
"EMEA", IF(ABS(SUM('Booked Order'[RM (LCUR)])) > 999999, FORMAT(SUM('Booked Order'[RM (LCUR)]),"€#,##0,,.##M;(€#,##0,,.##M)"),
IF(ABS(SUM('Booked Order'[RM (LCUR)])) > 999, FORMAT(SUM('Booked Order'[RM (LCUR)]),"€#,##0,K;(€#,##0,K)"),
FORMAT(SUM('Booked Order'[RM (LCUR)]), FORMAT(SUM('Booked Order'[RM (LCUR)]),"€#,##0;(€#,##0)")
)))
),
0
)
RETURN
IF(ISBLANK(Total),0,Total)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
hymieho Regular Visitor
Regular Visitor

Re: Custom FORMAT to add brackets/parentheses to negative numbers ALSO shows pre-pended hyphen/dash

And inexplicably it is working this morning?! Makes no sense but I'm going to leave this topic up to save someone their sanity. Smiley Happy

3 REPLIES 3
Super User
Super User

Re: Custom FORMAT to add brackets/parentheses to negative numbers ALSO shows pre-pended hyphen/dash

Check out it at here:

 

https://msdn.microsoft.com/en-us/library/ee634206.aspx

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
hymieho Regular Visitor
Regular Visitor

Re: Custom FORMAT to add brackets/parentheses to negative numbers ALSO shows pre-pended hyphen/dash

Right, the pattern in the documentation is what I am using to have negative numbers in brackets, and in the example there is no negative sign outside the brackets. Here is what I am getting for -935: -(€935) 

 

Why is the negative sign coming in when my format string for negative numbers does not say to put one?  

hymieho Regular Visitor
Regular Visitor

Re: Custom FORMAT to add brackets/parentheses to negative numbers ALSO shows pre-pended hyphen/dash

And inexplicably it is working this morning?! Makes no sense but I'm going to leave this topic up to save someone their sanity. Smiley Happy