Variance % shows 100% instead of zero%

[Anonymous]

Here is my variance formula: 

Variance = Calculate (Sum('Budget'[Approved Budget])-Calculate ('Measures (2)'[TotalAnc+Commit]))

 It works like I want it to, with the exception of returning 100% instead of 0% when the numbers are the same (the stoplight is correct with yellow=100%).  Is it possible to get both the zero percent AND still get the stoplight to work as I need?

Any help would be appreciated!

[v-yalanwu-msft · Accepted solution]

Hi， @Anonymous ；

Is it a matrix? You could use ISINSCOPE()  ,such as:

what% =
VAR _per =
    DIVIDE ( [EAC1], SUM ( [Approved Budget] ) )
RETURN
    IF ( ISINSCOPE ( 'Table'[Index] ), _per, IF ( _per = 1, 0, _per ) )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @Anonymous 

Can you share the codes you used for WHAT% measure?

You can use variable and IF statment to change 0 to 100%.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Anonymous]

What% = DIVIDE(

What%= Divide (('Measures (2)'[EAC]),

Sum(Budget[Approved Budget]))

I have no idea how to do a variable and IF statement.  I'm not very good with DAX.