cancel
Showing results for
Did you mean: Frequent Visitor

Variance % shows 100% instead of zero%

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!

1 ACCEPTED SOLUTION  Community Support

Hi， @DeeMeza ；

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.

3 REPLIES 3  Community Support

Hi， @DeeMeza ；

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

Hi @DeeMeza

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