Hiding/Blank : Infinity / NaN Error

[Anonymous]

Hey guys!

I am working with measures and I have got some Inifity / NaN Errors in my matrix which I want to show blank or hide them.

I usually use this measure for calculation:

SOTag Entw. = 
VAR __s = [SO/Tag 2020]/[SO/Tag 2019]
RETURN
IF ( __s  <> BLANK(), __s - 1 ) 

What do I need to add to those measures to blank those errors? Thanks!

[amitchandak]

@Anonymous ,

SOTag Entw. = divide([SO/Tag 2020],[SO/Tag 2019])

or

SOTag Entw. =
VAR __s = divide([SO/Tag 2020],[SO/Tag 2019])
RETURN
IF ( __s <> BLANK(), __s - 1 )

[AllisonKennedy]

The DIVIDE function will fix that for you

SOTag Entw. =
VAR __s =DIVIDE( [SO/Tag 2020], [SO/Tag 2019] )
RETURN
IF ( __s <> BLANK(), __s - 1 )

[Anonymous]

@AllisonKennedy @amitchandak 

I have tried both formulas, but the error still shows up 😞

[ibarrau]

Hi, I'm not sure about NaN because it sounds like that value is in the data before calculation. The problem with the formula is that you need to add a value in case of infinity or error for the division function. Like this:

DIVIDE(
    [SO/Tag 2020], [SO/Tag 2019]
    , BLANK()
)

That way it will return blank if the division contains an error or infinity.

Regards,

[Anonymous]

@ibarrau 

Thank you, thats working!

Another additional question:

What formulare do I have to use, if I want a development in %?

So usually my formula was:

([SO/TAG 2020]/[SO/Tag 2019])-1 

But if I put it on the on of your whole formulare it shows me -100% for the blanks.

Thanks!

Regards

[ibarrau]

Good! Assuming that the division is in a variable called "_division" you can try this:

VAR _division = ...
RETURN
IF(
    ISBLANK(_division),
    BLANK(),
    _division - 1
)

That way you can keep blanks as blanks.

Regards, 

[Anonymous]

@ibarrau 

Thanks. I am not very sure how the formula has to look at the end for example for this formula:

Entw.20 vs 19_DIOR = 
divide( sum('Jahr_Plan'[DIOR 20]),sum(Jahr_Plan[DIOR 19]),blank()) -1

Sorry - I am so nooby 😣

[ibarrau · Accepted solution]

Don't worry, you are just learning 🙂
I was talking about variables. Like this:

Entw.20 vs 19_DIOR = 
VAR _division = 
DIVIDE( 
    SUM('Jahr_Plan'[DIOR 20]),
    SUM(Jahr_Plan[DIOR 19]),
    BLANK()
) 
RETURN
IF(
    ISBLANK(_division),
    BLANK(),
    _division -1
)

Hope this time works !