Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 ?
Any help or advice - much appreciated 🙂
I am also looking for ways, but turn out it is easier to change the number formact to [<1]0.0%;""
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.
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 ...
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...
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
98 | |
90 | |
71 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |