Forum Discussion

RichJW's avatar
RichJW
Icon for Helper III rankHelper 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 ...
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 )
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
Icon for Helper III rankHelper 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 ;)

    • RichJW's avatar
      RichJW
      Icon for Helper III rankHelper III
      7 years ago

      Thanks again, appreciate it.

       

      It might be me, but I'm getting the red underline as soon as I add the table and value after the "IF(ISBLANK".

      I'll take another look on Monday, as need to leave the office soon - and I might have my thinking cap on then :smileyhappy:

       

      Thanks,

      Rich

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        It is ok.

        If you can print the formula bar so i can try to help you.

        see you.