Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
NickzNickz
Helper IV
Helper IV

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

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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" ) & ")"

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

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.... 

ValtteriN
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you so much. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.