Forum Discussion

JemmaD's avatar
JemmaD
Icon for Helper V rankHelper 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 pos...
Need Help
JemmaD's avatar
JemmaD
Icon for Helper V rankHelper 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
Icon for Super User rankSuper 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)
)
)

  • JemmaD's avatar
    JemmaD
    Icon for Helper V rankHelper V
    2 years ago

    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!