Forum Discussion

k8lyn22's avatar
k8lyn22
Regular Visitor
2 years ago
Solved

Help with summing only numbers and ignoring text values

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 AColumn BColumn C
12NA

6

NA87201
4762543NA
Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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])

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    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])

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.