Forum Discussion
Solved
Measure return Inf
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
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" ) & ")"
4 Replies
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/Thank you so much.
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" ) & ")"It's working as expected. Thank you so much....
