Dynamic formatting - Negative number showing brackets as well as negative sign

[ashwinkolte]

Hi 

I am using dynamic formatting string for a measure which has  positive and negetive numbers .I want the negetive numbers to be displayed in brackets instead of the negetive sign (-) . With the below code, for negetive numbers , brackets as well as negetive sign is getting displayed dsiplayed. How to get rid of this negetive sign for numbers which are already in brackets 

Code :

var result =

SWITCH(

       TRUE(),

       [M_Profit/Loss] >= 10000000 , "₹ "& ROUND(DIVIDE([M_Profit/Loss],10000000),2) & "Cr",

       [M_Profit/Loss] >= 100000 , "₹ "& ROUND(DIVIDE([M_Profit/Loss],100000),1) & "L",

        "₹ "& FORMAT([M_Profit/Loss],"#,#;(#,#);-")

     )

RETURN """" & result

Result for the measure : (showing brackets as well as negetive sign for negetive numbers 😞 

[Anonymous]

Hi @ashwinkolte ,

Thanks for the reply from Ritaf1983 .

Here is the sample data I created:

Column1
17,297
13,841
11,904
8,639
6,715
3,431
2,718
-9,772
-11,493
-13,287
-17,087
-24,449

Create a measure:

MEASURE = 
SWITCH (
    TRUE (),
    MAX ( 'Table'[Column1] ) >= 10000000, ROUND ( DIVIDE ( MAX ( 'Table'[Column1] ), 10000000 ), 2 ) & "Cr",
    MAX ( 'Table'[Column1] ) >= 100000, ROUND ( DIVIDE ( MAX ( 'Table'[Column1] ), 100000 ), 1 ) & "L",
    IF (
        MAX ( 'Table'[Column1] ) < 0,
        "-₹" & FORMAT ( MAX ( 'Table'[Column1] ), "#,0.00;(#,0.00);0" ),
        "₹" & FORMAT ( MAX ( 'Table'[Column1] ), "#,0.00;(#,0.00);0" )
    )
)

The final page visual effect is as follows:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[ashwinkolte]

Hi @Anonymous 

Thank you so much for your response, however with the solution you provided, negetive numbers are still showing brackets as well as negetive sign . If the negetive numbers are already in brackets , then there should not be negetive sign . How can we get rid of the negetive sign? . This is infact the actual problem 

[Ritaf1983]

Hi @ashwinkolte 
You can modify the formula of @Anonymous  to 

MEASURE =

SWITCH (

    TRUE (),

    MAX ( 'Table'[Column1] ) >= 10000000, ROUND ( DIVIDE ( MAX ( 'Table'[Column1] ), 10000000 ), 2 ) & "Cr",

    MAX ( 'Table'[Column1] ) >= 100000, ROUND ( DIVIDE ( MAX ( 'Table'[Column1] ), 100000 ), 1 ) & "L",

    IF (

        MAX ( 'Table'[Column1] ) < 0,

        "₹" & FORMAT ( MAX ( 'Table'[Column1] ), "#,0.00;(#,0.00);0" ),

        "₹" & FORMAT ( MAX ( 'Table'[Column1] ), "#,0.00;(#,0.00);0" )

    )

)
Result:

Modified file is attached :

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[ashwinkolte]

Dear @Ritaf1983  Thanks for quick reply. However please note I am trying to format a measure and not a table column . is this the problem ? . Also cant use MAX for the measure. But I tried the format you gave . Its still not working  

var result =

SWITCH(

       TRUE(),

       [M_Profit/Loss] >= 10000000 , "₹ "& ROUND(DIVIDE([M_Profit/Loss],10000000),2) & "Cr",

       [M_Profit/Loss] >= 100000 , "₹ "& ROUND(DIVIDE([M_Profit/Loss],100000),1) & "L",

       -- "₹ "& FORMAT([M_Profit/Loss],"#,#;(#,#);-")

       IF( [M_Profit/Loss] < 0 ,

                                "₹" & FORMAT ([M_Profit/Loss], "#,0.00;(#,0.00);0" ),

                                "₹" & FORMAT ([M_Profit/Loss], "#,0.00;(#,0.00);0" )

                                )

          )

RETURN """" & result

[Ritaf1983]

Hi @ashwinkolte 
It works with the measure too :

Try to follow the steps according to updated pbix

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[ashwinkolte]

Hi @Ritaf1983  Thank you for the response. yes it did work with measure as well but now there is  another problem. Now the measure is converted into string and hence it wont sort correctly as a number . See below the screenshots after sorting the  measure as well as the orignal number . Hence I was trying to use dynamically formatted strings where it will still retain its numerical nature even if apended with string like "Cr" , "L" or simply any other formatting , and hence sort correctly  . While using dynamically formatted strings  I was getting this issue of negetive sign with bracket. , which I sent originally 

[Anonymous · Accepted solution]

Hi @ashwinkolte ,

Thanks for the reply from Ritaf1983 .

You can't do what you want because you don't have a "-".

Here's an alternative:

You can create a measure:

Sort =
VAR _vtable =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[category],
            "_Sum", [sum_],
            "_sum2", [MEASURE]
        ),
        "Sort",
            RANKX (
                SUMMARIZE (
                    ALLSELECTED ( 'Table' ),
                    'Table'[category],
                    "_Sum", [sum_],
                    "_sum2", [MEASURE]
                ),
                [_Sum],
                ,
                DESC
            )
    )
RETURN
    MAXX (
        FILTER ( _vtable, [category] = SELECTEDVALUE ( 'Table'[category] ) ),
        [Sort]
    )

Sort sort to see the correct sorting.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Ritaf1983]

Hi @ashwinkolte 
Modify the format part for :
"#,##;(#,##)"

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly