Formatting If/divide dax to show -100 as a zero

[Jitmondo]

Hi All,

I have a table below which shows actuals against targets and a 3rd column which shows the variance.

I want the variance to exclude anything that is negative -100% but cant seem to get it in my if formula.

Any ideas on what I could do ?

I have tried the following to deal with blanks, then i nested another if to say if it = -100 then display blank/0 etc but it has no impact. Is this something I should have dealt with at the divide stage between actual and target possibly ?

IF Target Variance % = IF(ISBLANK( Sales Teams'[Target Variance %]),"-",'Sales Teams'[Target Variance %])

Any help or advice - much appreciated  🙂

[wangshuq]

I am also looking for ways, but turn out it is easier to change the number formact to  [<1]0.0%;""

[truptis]

Hi @Jitmondo , Then try this:
Result = var Target_Variance% = IF(ISBLANK( Sales Teams'[Target Variance %]),"-",'Sales Teams'[Target Variance %])
return 
If(Target_Variance% =-100, 0,Target_Variance%)

OR

Result = var Target_Variance% = IF(ISBLANK( Sales Teams'[Target Variance %]),"-",'Sales Teams'[Target Variance %])
return 
If(Target_Variance% <>-100,Target_Variance%,0)

Mark this as a solution & hit the thumbs up if it helps you. Thanks.

[Jitmondo]

Still no luck I am afraid, I feel like they should work to be honest but will look at the actual target variance measure to see if I can make amendments there ...

Target Variance % = calculate(divide(sum(Opportunity[Total Price (GBP)])-'Measures'[Target Sum],' Measures'[Target Sum]))

I think I need to somehow instrust the dax to complete the above calculation where there is no blank or zero in either actuals or targets if that makes sense.

[truptis]

Hi @Jitmondo , Try this:
Result = var Target_Variance% = IF(ISBLANK( Sales Teams'[Target Variance %]),"-",'Sales Teams'[Target Variance %])
return 
If(Target_Variance% >=0, Target_Variance%, 0)

@Jitmondo -> please mark this as a solution & hit the thumbs up if it helps you. Thanks.

[Jitmondo]

Thanks @truptis I tried this but it makes all negatives 0

I only want to -100 to be zero or blank as it means either actual or target is empty...

[AlexanderPrime]

I think they were close:

Try

Result = var Target_Variance% = IF(ISBLANK( Sales Teams'[Target Variance %]),"-",'Sales Teams'[Target Variance %])
return 
If(Target_Variance% =-1, 0,Target_Variance%)

Power BI treats percentages as decimals with 100% equalling 1. So if you were looking to only ignore negative 100%. Tell it to treat -1 as 0, otherwise bring up anything else.