cancel
Showing results for
Search instead 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.

Appreciate your Kudos!!

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.

## Helpful resources

Announcements

#### Check it Out!

Click here to read more about the December 2021 Updates!

#### Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

#### Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors