Text column number value need to change Thousand Separetor

[Anonymous]

Hi all,

1)   i have Table with one column Q1 score/status in this column as a text format (Mixing of number and text) i want to Thousand separetor in purticular number cells only is this possible?  kindly see the below image

2. i have a column name as a category in this column unique value only background color change (unique value whole row background color change) is this possible?  

Thank you in advance for your replies!

[v-yingjl · Accepted solution]

Hi @Anonymous ,

1) You can create this calculated column:

Q1 2020 Score / Status Result = 
VAR tab =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table' ),
            CONTAINSSTRING ( 'Table'[Q1 2020 Score / Status], "." )
        ),
        'Table'[Category],
        'Table'[Description],
        'Table'[Team],
        'Table'[Q1 2020 Score / Status],
        "RE", FORMAT ( CONVERT ( 'Table'[Q1 2020 Score / Status], DOUBLE ), "#,###.###" )
    )
RETURN
    IF (
        CONTAINSSTRING ( 'Table'[Q1 2020 Score / Status], "." ),
        MAXX (
            FILTER (
                tab,
                [Category] = EARLIER ( 'Table'[Category] )
                    && [Description] = EARLIER ( 'Table'[Description] )
                    && [Team] = EARLIER ( 'Table'[Team] )
            ),
            [RE]
        ),
        'Table'[Q1 2020 Score / Status]
    )

2) You can create this measure, use it as the conditional format background rule:

Color = 
IF (
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Category] ) ) = 1,
    "Red"
)

The final result will be like this:

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

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

[amitchandak]

@Anonymous , first check ISNUMBER , and the use format

https://docs.microsoft.com/en-us/dax/isnumber-function-dax

https://docs.microsoft.com/en-us/dax/format-function-dax

a new column = if(ISNUMBER([column]), format([column],"#,##0",[column])

I have not tested it

[Anonymous]

@amitchandak 

it's not working  

Column = if(

ISNUMBER([Q1 2020 Score / Status]),format([Q1 2020 Score / Status],"#,##0"),
"Not a number"

)