Skip Sum Computation when Power BI interprets column as string

[ftglobecrawler]

We are working with a live connection (to an Excel sheet) which makes it impossible to modify the data, I tried my best to clean up the source. I have the problem, that I have an empty column which is interpreted as STRING instead of numbers and therefore I cannot build the SUM. I try to skip the computation of the sum with an if command when the cells are empty, but it does not work.

This is the basic function I try to work with:

New Total  =

if (

    COUNT(List_A[Column A]) < 1,0,5)

It works that I get "0" if ther are no values, and I would get 5 if there are 2 or more values.

Now I repalce the "5" with the SUM command:

New Total  =
if (
    COUNT(List_A[Column A]) < 1,0, SUM(List_A[Column A]))

The value is not computated and Power BI complains that the SUM command is not possible to be executed as it is a string.

Anyone can help? I'm a bit confused, why does Power BI even bother about the SUM command if the if function tells it to skip it? 

[Sahir_Maharaj · Accepted solution]

Hello @ftglobecrawler,

Can you please try:

1. Convert Strings to Numbers

New Total = 
IF (
    COUNT(List_A[Column A]) < 1,
    0,
    SUMX(List_A, VALUE(List_A[Column A]))
)

2. Handle Non-Numeric Values

New Total = 
IF (
    COUNT(List_A[Column A]) < 1,
    0,
    SUMX(List_A, IF(ISNUMBER(VALUE(List_A[Column A])), VALUE(List_A[Column A]), 0))
)

Should you require further assistance, please do not hesitate to reach out to me.