Ignore subtraction when there is no value (null)

[Rotemshan]

Hi, 

I did a short formula to calculate the trend with customers, 

for that, I have the last score and the current score. 

I did a subtraction between the last score and the current score.

If the answer is positive - the trend is down (Last score is 10, Current score is 9 ==> 10-9 = 1)

If the answer negative - the trend is up(Last score is 8, Current score is 9 ==> 8-9 = -1)

I'm using it in a conditional formatting 

My problem is when there is "null" in the "Last score". the formula considers this as a 0 instead of ignoring it. 

How can I make the formula ignore when there is a null in the "Last score"? 

** The score column is defined as int

[Rotemshan]

I think I got what you have tried to say

I applied it this way: 

Last NPS Minus NPS =

if( ISBLANK (SUM('nps'[Last NPS Score]) - SUM('Response_Details'[Likely to Recommend NPS.1])), BLANK(),

(SUM('nps'[Last NPS Score]) - SUM('Response_Details'[Likely to Recommend NPS.1])))

Is this correct? 

After I use the formula, I still have a problem with the conditional formatting as shown in the screenshot below, 
the third guy provided the score only the second time, but it looks like the formula considers the trend as positive instead of ignoring it (and put blank)

In a matter of fact, I'm expecting user 3 to be blank 

and user 4 to have "Amber" Icon

[TheoC]

@Rotemshan I've taken your formula and adjusted slightly below:

Last NPS Minus NPS = 

VAR _1 = ISBLANK ( 'nps'[Last NPS Score] )
VAR _2 = SUM ('nps'[Last NPS Score] )
VAR _3 = SUM ( 'Response_Details'[Likely to Recommend NPS.1] )
RETURN
IF ( _1 , BLANK() , _2 - _3 )

Hopefully this is what you're after?

Theo 🙂

[Rotemshan]

For some reason, I can't pick the "Last NPS score" under the ISBLANK function

The column is configured as INT and there are several "null" 

Any idea?

[Rotemshan]

Sounds good ..

How do I apply conditional formatting based on that? 

What are the possible values of Last NPS Minus NPS?

[TheoC]

@Rotemshan The name "Last NPS Minus NPS" is just what I called the title of the formula. You can call it whatever you want. 

In terms of conditional formatting, you apply it the exact same way you normally would.

1. Pull the visual you want from the visual options

2. Add the Measure.

3. Click on Conditional Formatting.

Cheers mate.

Theo

[TheoC]

HI @Rotemshan 

You can create an IF ISBLANK then BLANK() else...

Calculated Column =

IF ( 
     ISBLANK ( Table[Last NPS Score] ) , BLANK() , 
     SUM ( 'nps'[Last NPS Score] ) - SUM ( 'Response_Details'[Likely to Recommend NPS.1] ) 
    )

Hope this helps 🙂

Theo