Forum Discussion

Khurram_Ahmed's avatar
Khurram_Ahmed
Frequent Visitor
2 years ago
Solved

Can't use SUM with VALUE function

I am trying to use a measure to first check if a column has numeric values and only then sum those values.

Here is my code:

 

 

IF(
    ISERROR( VALUE( SUM( 'Spreadsheet Cell'[Value] ) ) ),
    BLANK( ),
    VALUE( SUM( 'Spreadsheet Cell'[Value] ) )
)

 

 

However, it is giving this error:

The above formula works if I replace SUM with MAX (but only gives the max value, I am interested in getting the SUM).

Any help with this is appreciated. Thanks!

Data Protection
  • smpa01's avatar
    smpa01
    2 years ago

    Can you try this Khurram_Ahmed 

     

    MEASURE =
CALCULATE (
    SUMX (
        ADDCOLUMNS ( 'Table', "@double", CONVERT ( 'Table'[Column1], DOUBLE ) ),
        [@double]
    ),
    FILTER (
        VALUES ( 'Table'[Column1] ),
        NOT ( ISERROR ( CONVERT ( 'Table'[Column1], DOUBLE ) ) )
    )
)

3 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    2 years ago

    Can you try this Khurram_Ahmed 

     

    MEASURE =
CALCULATE (
    SUMX (
        ADDCOLUMNS ( 'Table', "@double", CONVERT ( 'Table'[Column1], DOUBLE ) ),
        [@double]
    ),
    FILTER (
        VALUES ( 'Table'[Column1] ),
        NOT ( ISERROR ( CONVERT ( 'Table'[Column1], DOUBLE ) ) )
    )
)
  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    Hi Khurram_Ahmed,

     

    Please try this:

    IF(
    ISNUMBER( SUM( 'Spreadsheet Cell'[Value] ) ),
    SUM( 'Spreadsheet Cell'[Value] ),
    BLANK()
)