Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Skip Sum Computation when Power BI interprets column as string

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's avatar
    Sahir_Maharaj
    3 years ago

    Hello Anonymous,

     

    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.

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    Hello Anonymous,

     

    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.