Custom Format Measure column to add brackets/parentheses to negative numbers

[Alex_SD]

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

[Greg_Deckler]

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.

[Alex_SD]

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

[martina]

Hi,

it does work with calculate function. But just be aware that using format function converts the value into a string / text data type.

I am using this function for the measure displayed in the matrix table with the option "no calculation" and not having any problem. Unfortunately such measure cannot be used in any line or stacked columns charts.

[Sean]

@martina

You know I actually have exactly the opposite problem. The US $ defaults to showing negatives like so ($100)

In Excel we have better Formatting options as you know and can see in the picture

But I was just surpised to find out you don't even have this option in the Excel Formatting for the pound 

We need this option to be available for all currencies in PBI

And I hope they add it someday.....

[Greg_Deckler · Accepted solution]

Do it in two steps:

Measure 2 = VAR myCalc = CALCULATE(100) RETURN FORMAT(myCalc,"£#,##0;(£#,##0)")