Sum of rows referencing another column's values when datatype is text

[Djotis]

Hello everyone, 

I have a table that looks like below. I need to write a measure that calculates the sum of values from the Value column based on the values AM35 up to AM41 in the Field Code column, hence the returned result should be 72. My additional problem is that the Value column is of datatype text because it contains mixed text and numbers therefore I cannot change the datatype. Any suggestions would be appreciated.

[v-jingzhang · Accepted solution]

Hi @Djotis 

You can add a custom column with below code to get only the number values. For other text type values, return null in the custom column. Then convert this new column to Number type. You can then create a measure with SUM function to get the sum of these values. 

if Number.From(Text.AfterDelimiter([Field Code],"AM")) >= 36 and Number.From(Text.AfterDelimiter([Field Code],"AM")) <= 41 then [Value] else null

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Djotis]

Sorry the expected result should be 135. Made a mistake with the calculation.