Forum Discussion
Solved
Custom Format Measure column to add brackets/parentheses to negative numbers
Hi all,
My finance department has asked that the currency columns be format from -£5,000 to (£5,000)
I've tried a few options but I can't get this to work when the column is a measure...
Variance =
FORMAT (
CALCULATE (
SUM ( Margin[Cont Margin] )
- SUM ( Margin[Cont Budget] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Week] <= MAX ( 'Calendar'[Week] ) )
),
"£#,##0;(£#,##0)"
)The column is a calculating a cumulative variance over the weeks which needs formatting. The formatting works for any other column except a measure...
Any tips would be great, cheers
Do it in two steps:
Measure 2 = VAR myCalc = CALCULATE(100) RETURN FORMAT(myCalc,"£#,##0;(£#,##0)")
5 Replies
Perhaps the issue is with your calculation? I did something very simple:
Measure 2 = FORMAT(-100,"£#,##0;(£#,##0)")
This came out as:
(£100)
Positive numbers came out as:
£100
Seemed to be right.
Unfortunately this doesn't work with Calculate...
The formula for the measure works as expected, but as soon as I add the FORMAT(CALCULATE( it breaks.
Error in Table
Do it in two steps:
Measure 2 = VAR myCalc = CALCULATE(100) RETURN FORMAT(myCalc,"£#,##0;(£#,##0)")
