How to create conditional formatting using "sum of column" for a column whose data type is "text"

[INPRABHAKARS]

I have a report which has a column "Africa" of data type "text" containing following values.

Africa

45.6

67.5

NA

NV

null

I want to create conditional formatting(shown below) where I can use sum of the column "Africa" to change background color in the visual.

How do I achieve that?

Currently, because the datatype is text, I am not getting option to use Sum of "Africa" for the conditional formatting.

This is how it looks now.

[manvishah17]

Steps of Power Query :
Duplicate the column then filter out values as NA,NV and then change data type to Whole Number.

let
    Source = Excel.Workbook(File.Contents("C:\Users\**bleep**al Clinic(Rutvi)\Downloads\crime.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Month"}, {"Column3", "Date"}, {"Column2", "Africa"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Africa", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Africa", "Africa - Copy"),
    #"Filtered Rows" = Table.SelectRows(#"Duplicated Column", each ([#"Africa - Copy"] <> "NA")),
     #"Filtered Rows1" = Table.SelectRows(#"Duplicated Column", each ([#"Africa - Copy"] <> "NV"))
in
    #"Filtered Rows1"

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!

[manvishah17]

Yes, I was saying the same thing. Like conditional formatting via Measure, you can use IF or SWITCH DAX according to your preference. But you just can't assign a color to every value, as the values of columns might be numerous. If you want to showcase your values as the sum of them against any value, then you need to use MAX(Sum), MIN(Sum), or something similar for specific colors. 
So @INPRABHAKARS , Please tell me what exactly you want to plot in a visual. 
Like against which column you want your sum and conditinal formatting.

[INPRABHAKARS]

Hi,

The values in Africa column is as follows

I want to be able to create conditional formatting as per the following image.

[v-zhouwen-msft]

Hi @INPRABHAKARS ，

Use the following DAX expression to create a column(You can make your own assumptions about the values of “NA” and “NV”, just make sure you don't duplicate any other numbers in the column.)

Column = SWITCH(TRUE(),
CONTAINSSTRING("NA",[Africa]),9999,
CONTAINSSTRING("NV",[Africa]),99999,
VALUE([Africa]))

[manvishah17]

Hi @INPRABHAKARS , 
Thanks for the information , 
You can either replace NA, NV with some unique values or simply apply this steps: 
Copy the africa column and remove values Na and NV , either from data source or in power query
Make the data type Integer and then go to conditional formatting and add the rules according to your need. 
You can also use this measure , 

Conditional Formatting =
IF (
    [TotalAfrica] = 1,
    "red",
    IF (
        [TotalAfrica] = 4,
        "green",
        IF (
            [TotalAfrica] = 0,
            "white",
            IF ( [TotalAfrica] >= 5 && [TotalAfrica] < 100, "blue" )
        )
    )
)

TotalAfrica = 
SUM('Crime Data'[Africa])

See for your refernce , I have created sample and plot the sum against date so see conditinal formatting.

 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!

[v-zhouwen-msft]

Hi @manvishah17 ,thanks for the quick reply, I'll add further.

Hi @INPRABHAKARS ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

Measure = 
 VAR _a = SELECTEDVALUE('Table'[Africa])
 VAR _b = SWITCH(TRUE(),
 _a = "45.6","Red",
 _a = "67.5","Red",
 _a = "NA","White",
 _a = "NV","Green")
 RETURN _b

2.Setting the Conditional Format

3.Final output

[INPRABHAKARS]

Hi. The table I showed just contains some values. But the actual table contains different values as follows.

So, the above DAX expression won't work in this case. How should I proceed further?

[manvishah17]

Okay then do one thing , make a copy of this africa column and do the steps i suggested before or else try to replace NA NV WITH SOME UNIQUE NUMBER OR try to achieve rules of conditional formttiang of any text values. 
If you dont mind can you just share me your data or part of it which are included for conditional formatting so that we  can try it by achieving it with a measure.

[manvishah17]

Do NA ,NV hold some significance , if not then replace it with 0 or remove such values. 
If they are important  and the column can't change to integer data type

then you can't apply sum rules on any text values columns.

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

[INPRABHAKARS]

NA and NV hold significance. I need to show NA or NV in the graph where applicable. There are also 0 values in the same column as well which needs to be shown in the graph

[manvishah17]

Hi @INPRABHAKARS , I think you need to change the data type of your column to Whole Number. After that you can apply any RULE OF Aggregration. 
See this is my 'Africa' named column after changing it  data type to integer , I can get SUM option for rules of conditional formatting .

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

[INPRABHAKARS]

Hi,

The column "Africa" also contains text such as "NA" and "NV".  It gives an error when I change the datatype to whole number