Can't get negative basis points in parentheses to show properly

[peterg0417]

Just got a request from leadership to change the way our basis points are displayed. A third party vendor created this model and I'm tasked with managing it going forward.

Here is the DAX formula they created:

YTD Growth =

VAR FormatToUse =

    IF (

        SELECTEDVALUE ( 'Key Statistics'[Key Statistic] ) = "NAV",

        "0.0%;(0.0%);-",

        "0bps"

    )

RETURN

    IF (

        OR ( [YTD Growth Number KS] = 0, ISBLANK ( [YTD Growth Number KS] ) ),

        "-",

        FORMAT (

            [YTD Growth Number KS]

                * IF ( FormatToUse  = "0bps", 10000, 1 ),

            FormatToUse

        )

    )

The result of this query is:

When it's a percentage, the it displays the correct format. Eg. (55.5%) for a negative 55%. But for basis points (bps) I can't get it to work when I try modifying the variable to this:

VAR FormatToUse =

    IF (

        SELECTEDVALUE ( 'Key Statistics'[Key Statistic] ) = "NAV",

        "0.0%;(0.0%);-",

        "0bps;(0bps)"

    )

RETURN

Then modify the IF statement further on:

IF (

        OR ( [YTD Growth Number KS] = 0, ISBLANK ( [YTD Growth Number KS] ) ),

        "-",

        FORMAT (

            [YTD Growth Number KS]

                * IF ( FormatToUse IN { "0bps", "(0bps)"}, 10000, 1 ),

            FormatToUse

        )

    )

The result I get is this:

The expected result where it's highlighted is (10,000bps) and (8,182bps). For some reason, in the IF statement, it's not recognizing the negatives, and it's just multiplying by 1, as opposed to 10000 like it's supposed to do.

Any ideas on how to get this to work?

Thx

[Anonymous]

Hi @peterg0417 ,
Try modifying your IF statement as below: 

IF (

        OR ( [YTD Growth Number KS] = 0, ISBLANK ( [YTD Growth Number KS] ) ),

        "-",

        FORMAT (

            [YTD Growth Number KS]

                * IF ( FormatToUse ="0bps;(0bps)", 10000, 1 ),

            FormatToUse

        )

    )

[Greg_Deckler · Accepted solution]

@peterg0417 Should be this: 

 * IF ( FormatToUse IN { "0bps;(0bps)"}, 10000, 1 ),

[peterg0417]

Ahh yes, that makes sense

This whole custom formatting is very new to me so thank you for the quick solution