cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper III

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

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  🙂

6 REPLIES 6
New Member

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

Community Champion

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.

Helper III

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

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.

Helper III

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

Solution Supplier

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.

Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors