Help with summing only numbers and ignoring text values

[k8lyn22]

I have 3 columns in a table that have both numeric values and text values.  I have written the same measure to sum only if the value is numeric, but I am getting an error for only one column.  It is trying to convert "NA" to number in one column (Cannot convert value 'NA' of type Text to type Number), but it is ignoring the same "NA" values in the other columns and successfully summing.  The only difference in the measure below is changing Column A to B and C. 

SUMX( FILTER( 'All Combined', ISNUMBER( IFERROR( VALUE( 'Table 1'[Column A] ), FALSE() ) ) = TRUE() ), VALUE( 'Table 1'[Column A] ) )

Table

Column A	Column B	Column C
12	NA	6
NA	87	201
476	2543	NA

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, you should replace the NA with null.  Thereafter, you can simply write this measure

Measure = sum('Table 1'[Column A])

[v-xuxinyi-msft · Accepted solution]

Hi @k8lyn22 

I would like to apologize for the belated reply.

Have you solved your problem? If not, try the following:

Create several measures as follow

ASumOnlyNumbers = 
SUMX( 
    FILTER( 
        'Table', 
        ISNUMBER( 
            IFERROR( 
                VALUE( 'Table'[Column A] ), 
                BLANK() 
            ) 
        ) 
    ), 
    IFERROR( 
        VALUE( 'Table'[Column A] ), 
        BLANK() 
    ) 
)

BSumOnlyNumbers = 
SUMX( 
    FILTER( 
        'Table', 
        ISNUMBER( 
            IFERROR( 
                VALUE( 'Table'[Column B] ), 
                BLANK() 
            ) 
        ) 
    ), 
    IFERROR( 
        VALUE( 'Table'[Column B] ), 
        BLANK() 
    ) 
)

CSumOnlyNumbers = 
SUMX( 
    FILTER( 
        'Table', 
        ISNUMBER( 
            IFERROR( 
                VALUE( 'Table'[Column C] ), 
                BLANK() 
            ) 
        ) 
    ), 
    IFERROR( 
        VALUE( 'Table'[Column C] ), 
        BLANK() 
    ) 
)

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

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

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, you should replace the NA with null.  Thereafter, you can simply write this measure

Measure = sum('Table 1'[Column A])