CODEWITHSUNDEEP
powerbidax
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

DAX number format with a plus or minus sign

I want to format DAX measure so that value 0.105 is displayed as +10.5%, and value -0.105 is displayed as -10.5%. Important for me is the plus sign. I failed using FORMAT(measure, "+0.0%").

I cannot find anything helpful in FORMAT function doc: https://msdn.microsoft.com/query-bi/dax/custom-numeric-formats-for-the-format-function

The only solution I can think of is handling it with IF or SWITCH function. Is there a way to avoid it?

IF(variable>0, "+"&variable, variable)

Upvotes: 7

Views: 23201

Answers (3)

Rick de Groot - gorilla.bi
Rick de Groot - gorilla.bi

Reputation: 406

I'm a bit late to the game perhaps. And yes, formatting negative numbers in parentheses was a bit difficult in Power BI before. As other answers suggest, it would involve the FORMAT function. But today you can use custom formatting for a measure. For example "#,##0.0, (#.##0.0)". Hope that helps. You can find a more elaborate explanation here: 

Format Negative values between Parentheses

Keep crushing it!

Rick

Upvotes: 2

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

I keep RADO's answer as accepted. I ended up with solution, which overcomes the problem of very small negative fractional values - for example value of -0.0001 which may become positive zero after rounding by FORMAT function (see discussion in our comments). 

Plus Minus Growth =
SWITCH (
    TRUE (),
    ISBLANK ( [Growth] ), BLANK (),
    [Growth] >= 0, FORMAT ( [Growth], "+0.0 % ▲;-0.0 % ▼" ), -- positive here
    [Growth] <  0, FORMAT ( [Growth], "-0.0 % ▼;-0.0 % ▼" ), -- negative here
    BLANK ()
)

Negative fractional numbers which are rounded to positive zero by FORMAT function fall in the first part of the code "-0.0 % ▼;-0.0 % ▼"

Upvotes: 0

RADO
RADO

Reputation: 8148

You need to create custom format for both positive and negative numbers:

Formatted Value = FORMAT( [Measure], "+0.0%;-0.0%")

Result: enter image description here

In general, custom format string consists of 4 parts, separated by ;

Positive values; Negative values; Zero values; Text values

One section is required, others are optional. So, to avoid + in front of zero, a full code might be:

Formatted Value = FORMAT( [Measure], "+0.0%;-0.0%;"0")

Upvotes: 12

Related Questions