Can someone tell me what's wrong with my DAX code? (adding bps to a variance)

[EvaHello]

This is my code to add bps to a percentage variance.  It works when the variance is negative but not when its positive.  Could anyone tell me what im doing wrong/how to fix it?  

% Variance Actual vs FC =

VAR MetricType = SELECTEDVALUE('Results (2)'[Period])

VAR Actual = SELECTEDVALUE('Results (2)'[MTD Actual])

VAR FC = SELECTEDVALUE('Results (2)'[MTD FC])

RETURN

IF(

MetricType = "Percentage",

FORMAT((Actual - FC)*10000, "0;(0)" & "bps"),

FORMAT(Actual - FC, "0.0;(0.0)")

)

[EvaHello]

Hi @Nasif_Azam , thank you for replying.  I tried your code but im getting a syntax error saying the syntax for bps is incorrect.  Can you explain why?  Your logic does work but i cant see what im doing wrong!

[Nasif_Azam · Accepted solution]

The syntax error you’re encountering is due to how escape characters are being handled within the FORMAT function’s format string in DAX. 

The Problem in this expression: FORMAT((Actual - FC) * 10000, "0\"bps\";\"(\"0\")bps\"") 

You're attempting to use escaped double quotes \" inside a DAX string, but DAX doesn't use backslashes (\) to escape quotes. Instead, DAX escapes double quotes by doubling them, like this: "".

You should replace all \" with "". Corrected version of your DAX expression:

% Variance Actual vs FC =
VAR MetricType = SELECTEDVALUE('Results (2)'[Period])
VAR Actual = SELECTEDVALUE('Results (2)'[MTD Actual])
VAR FC = SELECTEDVALUE('Results (2)'[MTD FC])
RETURN
IF(
    MetricType = "Percentage",
    FORMAT((Actual - FC) * 10000, "0""bps"";(""0"")bps"),
    FORMAT(Actual - FC, "0.0;(0.0)")
)

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[EvaHello]

Thank you so much @Nasif_Azam , i tweaked it a bit as still wasnt working for negatives but it works now!!  Thank you, youre a genius

IF(

MetricType = "Percentage",

    FORMAT((Actual - FC) * 10000, "0""bps"";""(""0"")bps"""),

    FORMAT(Actual - FC, "0.0;(0.0)")

[Rearmem]

Thanks for sharing your DAX. I think the issue comes from the way you’re formatting the output for positive numbers — especially in the FORMAT function. In DAX, the format string "0;(0)bps" means:

Use 0 for positive numbers

Use (0)bps for negative numbers

So when the result is positive, it just shows a plain number without the bps suffix.
space waves

[Nasif_Azam]

Hey @EvaHello ,

The issue in your DAX code lies in the FORMAT function, particularly this part:

FORMAT((Actual - FC)*10000, "0;(0)" & "bps")

What's Wrong

• "0;(0)" & "bps" is a custom format string for FORMAT.

• In DAX, format strings like "0;(0)" define:

  • Positive numbers: "0"

  • Negative numbers: "(0)"

You're trying to append "bps" to the result, but:

• This only works correctly for negative numbers, because you're formatting it as "(X)bps".

• For positive numbers, "0" returns just the number without "bps" and you aren't appending "bps" manually for positive numbers.

Fix

To make "bps" appear for both positive and negative values, you can modify the format string to:

"0\"bps\";\"(\"0\")bps\""

The corrected code:

% Variance Actual vs FC =
VAR MetricType = SELECTEDVALUE('Results (2)'[Period])
VAR Actual = SELECTEDVALUE('Results (2)'[MTD Actual])
VAR FC = SELECTEDVALUE('Results (2)'[MTD FC])
RETURN
IF(
    MetricType = "Percentage",
    FORMAT((Actual - FC) * 10000, "0\"bps\";\"(\"0\")bps\""),
    FORMAT(Actual - FC, "0.0;(0.0)")
)

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam