Measure return Inf

[NickzNickz]

Hi ...

I have below measure:

Variance Projection = 

VAR
    _Nos = [Sum Achievement %] - [Sum Projection]

VAR
    _percentage =
        _Nos / [Sum Projection]

VAR
    _direction =
        SWITCH(
                TRUE(),
                [Sum Achievement %] >= [Sum Projection], UNICHAR(9650),
                [Sum Achievement %] < [Sum Projection], UNICHAR(9660)
        )

Return 
    _direction & " " & FORMAT(_percentage, "0.00%") & " (" &FORMAT(_Nos, "0.00") &")"

If the Sum Projection has a value the result return as expected.

However, if empty/0 its returns Inf ... How can I resolve that?

At least the result return value of 0.00 instead of Inf.

Thank you. 

Regards,

NickzNickz

[johnt75 · Accepted solution]

You can use COALESCE to return 0 instead of blank,

Variance Projection =
VAR _Achievement = [Sum Achievement %]
VAR _Projection = [Sum Projection]
VAR _Nos = _Achievement - _Projection
VAR _percentage =
    COALESCE ( DIVIDE ( _Nos, _Projection ), 0 )
VAR _direction =
    SWITCH (
        TRUE (),
        _Achievement >= _Projection, UNICHAR ( 9650 ),
        _Achievement < _Projection, UNICHAR ( 9660 )
    )
RETURN
    _direction & " "
        & FORMAT ( _percentage, "0.00%" ) & " ("
        & FORMAT ( _Nos, "0.00" ) & ")"

[NickzNickz]

It's working as expected. Thank you so much.... 

[ValtteriN]

Hi,

Instead of using "/" use DIVIDE function. This will avoid any issued with dividing by 0. 

E.g. DIVIDE(_Nos, [sum projection])

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[NickzNickz]

Thank you so much.