How to clear off Infinity and NaN value?

[Anonymous]

Hi. I wanted to create another column based on this column.

I understand that NaN & infinity is because my calculations are when 0 divided by value, if I'm not mistaken, right? So what I want is another column like this. 
If  Loss wrt med doesn't have value (whether it is NaN or infinity), I want to return 0. Else, it will return itself. My output should be like this. 

[v-alq-msft]

Hey, @zhrhr

You can try to modify the measures as follows. The pbix file is attached at the end.

Minus MED TRIP IN = 
SUMX(
    Sheet1,
    DIVIDE(
       DIVIDE(Sheet1[sect_top],3.28084,0),
       Sheet1[TRIP IN SPEED BELOW MED]
    ) - 
    DIVIDE(
        DIVIDE(Sheet1[sect_top],3.28084,0),
        Sheet1[median]
    )
) + 0

LOSS wrt MED (TRIP IN) = ABS(SUMX(Sheet1,DIVIDE(Sheet1[Minus MED TRIP IN],24,0)*(Sheet1[Cost]*1000))) + 0

Result:

Best regards

Allan

If this post helps, then consider Accept it as the solution to help other members find it faster.

[PhilipTreacy]

Hi @Anonymous 

Download your PBIX file with solution

This measure still had some division but wasn't using DIVIDE

Minus MED TRIP IN = SUMX(Sheet1, DIVIDE(DIVIDE(Sheet1[sect_top],3.28084,0),Sheet1[TRIP IN SPEED BELOW MED],0) - DIVIDE(DIVIDE(Sheet1[sect_top],3.28084,0),Sheet1[median],0)) + 0

Regards

Phil 

[Anonymous]

@PhilipTreacy  @amitchandak It doesn't work for me. Maybe because to get to this formula, I have 3 other formulas that use division to get to this final formula. Can I just create an if-statement? Just so that I can put them side by side. One showing NaN/Infinity, next showing it was replaced by 0(like the picture below)?

[amitchandak]

@Anonymous ,Can you share a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak @PhilipTreacy  This is my pbix link. The output should be the same as picture shown before. Glad if you would be able to help me https://1drv.ms/u/s!AoKh3nlpMpSOgmH-Lf_mTn3W2gXy?e=ZzkwS7

[PhilipTreacy]

Hi @Anonymous 

Like this

LOSS wrt MED (TRIP IN) = ABS( SUMX(ODBS_FTO_BHA, DIVIDE(ODBS_FTO_BHA[Minus MED TRIP IN], 24, 0) * (ODBS_FTO_BHA[RIG_COST]*1000)))

Regards

Phil

[amitchandak]

@Anonymous You can use divide

divide([A1],[A2],0)

[Anonymous]

@amitchandak if my formula looks like this, how should I change it? 

LOSS wrt MED (TRIP IN) = ABS(SUMX(ODBS_FTO_BHA,(ODBS_FTO_BHA[Minus MED TRIP IN]/24)*(ODBS_FTO_BHA[RIG_COST]*1000)))

[amitchandak]

@Anonymous , divide by 24, can not cause Divide by 0 error. +0 can handle blank here 

LOSS wrt MED (TRIP IN) = ABS(SUMX(ODBS_FTO_BHA,(ODBS_FTO_BHA[Minus MED TRIP IN]/24.0)*(ODBS_FTO_BHA[RIG_COST]*1000)))+0

or try

LOSS wrt MED (TRIP IN) = ABS(SUMX(ODBS_FTO_BHA,Divide(ODBS_FTO_BHA[Minus MED TRIP IN],24.0,0)*(ODBS_FTO_BHA[RIG_COST]*1000)))+0