Forum Discussion

RichJW's avatar
RichJW
Helper III
7 years ago
Solved

Adding ISBLANK into SUM Formula

Hi,

 

I've a SUM formula, as below, which finds the variance between two sets of numbers.

Exposure Difference = SUM('Risks'[Exposure]) - SUM('Risks Prev'[Exposure])

 

However, the second column of data (Risks Prev) does not always contain data, therefore will often be blank. I would like it to be ignored when there is no data, as currently it is counted as zero.

So, if the SUM of the first column is 20 and the second is blank, I want the result to be blank, NOT 20 as it currently is.

I've tried the below formula and variants of it, with no success.

Exposure Difference = IF(ISBLANK(SUM('Risks'[Exposure])) - IF(ISBLANK(SUM('Risks Prev'[Exposure]))))

 

Many thanks,

Rich

 

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi RichJW ,

     

    Try this one.

     

    Exposure Difference =
VAR sumr =
    SUM ( 'Risks'[Exposure] )
VAR sumri =
    SUM ( 'Risks Prev'[Exposure] )
RETURN
    IF ( ISBLANK ( sumri ), 0, sumr - sumri )

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi buddy,

     

    Try this:

     

    Exposure Difference = IF(ISBLANK(SUM('Risks'[Exposure]));0;SUM('Risks'[Exposure]) - SUM('Risks Prev'[Exposure])).

     

    Any questions, ask ;)

    • RichJW's avatar
      RichJW
      Helper III
      7 years ago

      Hi f_lopes,

       

      Thanks a lot for the quick response.

       

      I've pasted that over my existing formula and it errors showing the below underlined bits as the issue  -saying the syntax is incorrect.

      Exposure Difference = IF(ISBLANK(SUM('Risks'[Exposure]));0;SUM('Risks'[Exposure]) - SUM('Risks Azure'[Exposure]))

       

      I tried changing the semi colons to commas, however this removed all the data from the 2 columns in the table.

       

      Cheers,

      Rich

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Sorry buddy, i got it wrong the first time.

        Let's try again.

         

        So, the ISBLANK() formula will always return "true" or "false".

        the SUM() does a total of the column, not line by line, so you need to use SUMX().

        The rigth way to do this would be:

         

        Exposure Difference = 

         IF(ISBLANK(Table1[Value 2]);0;CALCULATE(SUMX(Table1;Table1[Value])-SUMX(Table1;Table1[Value 2])))

         

        this would validate your second column, the "risk" one, and give the value you want.

         

        Any questions, ask ;)

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi RichJW ,

     

    Try this one.

     

    Exposure Difference =
VAR sumr =
    SUM ( 'Risks'[Exposure] )
VAR sumri =
    SUM ( 'Risks Prev'[Exposure] )
RETURN
    IF ( ISBLANK ( sumri ), 0, sumr - sumri )
    • RichJW's avatar
      RichJW
      Helper III
      7 years ago

      Hi v-frfei-msft,

      Many thanks, this has worked beautifully.

       

      Anonymous , greatly appreciate your help and efforts, however it still brought up an error when I added my table name, however it could still be my issue and not your formula.

       

      It's sorted now anyway, so thank you both very much.

      Rich