Forum Discussion

JemmaD's avatar
JemmaD
Helper V
2 years ago

Sum a column which contains multiple data types

Hi there,

I have a column which contains decimals, dates and text. I want to sum it when the data type is decimal, and ideally just show the text or date values unaggregated - but if this is not possible, to just ignore non-decimal values.

Is this possible with some sort of IFERROR? I've tried but am not getting the right syntax.

 

My column name is [value] and my table name is 'Exception Details'

Need Help

4 Replies

  • Gabry's avatar
    Gabry
    Super User
    2 years ago

    Hello,

    did you try something like this?

     

    SumOfDecimals =
    SUMX(
    'Exception Details',
    IF(
    ISNUMBER('Exception Details'[value]),
    'Exception Details'[value],
    0
    )
    )

    • JemmaD's avatar
      JemmaD
      Helper V
      2 years ago

      Thanks Gabry - the measure no longer errors, however it just shows 0 for everything, even when I filter the value column so I know it's just decimal numbers. I think the issue could be because the column type is text, the IF(ISNUMBER is not picking up the numbers. 

      • Gabry's avatar
        Gabry
        Super User
        2 years ago

        I understand, can't you set up the data type to any in power query? Just wondering. If not maybe you can try convert it

         

        SumOfDecimals =
        SUMX(
        'Exception Details',
        IF(
        ISERROR(CONVERT('Exception Details'[value], DOUBLE)),
        0,
        CONVERT('Exception Details'[value], DOUBLE)
        )
        )