Sum a column which contains multiple data types

[JemmaD]

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'

[Gabry]

Hello,

did you try something like this?

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

[JemmaD]

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]

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

[JemmaD]

I found a data type column, so i've actually used M Query conditional columns to create separate columns for the different data types. Then my measures can work independently. Thanks!