How to do <100% in custom column

[siddrow]

Hi

I've tried the below, however it isnt working. The status column is text type and the % complete is % (see below screenshot). I basically want to say if the status column is "Status" and the % completed column is less than 100% then "Discrepancy"

if [Status]="Completed" and [#"% Complete"] <1 then "Discrepancy"

else if [Status]="In Progress" and [#"% Complete"]=1 then "Discrepancy"

else if [Status]="Overdue" and [#"% Complete"]=1 then "Discrepancy"

else if [Status]="Not Started" and [#"% Complete"]=1 then "Discrepancy"

else "")

thank you in advance

[PhilipTreacy]

Hi @siddrow 

Download example PBIX file

How exactly is it not working?

The only problem I can find is that you don't need a closing parenthesis )

This code works for me

if [Status]="Completed" and [#"% Complete"] <1 then "Discrepancy"

else if [Status]="In Progress" and [#"% Complete"]=1 then "Discrepancy"

else if [Status]="Overdue" and [#"% Complete"]=1 then "Discrepancy"

else if [Status]="Not Started" and [#"% Complete"]=1 then "Discrepancy"

else ""

Regards

Phil

[siddrow]

Thanks for the response Phil. I ended up working it out - I had to change the <1 to <1.0 and it fixed it

[PhilipTreacy]

Hi @siddrow 

Changing 1 to 1.0 shouldn't have had bearing on the problem.  If you check my example file you'll see the code works fine using just 1.

You sure the problem wasn't something else, like the ) at the end of the code?

Regards

Phil

[siddrow · Accepted solution]

Sorry, I didn't include my whole formula initially. This is it below - so the ) wasn't the issue. The <1.0 worked and is bringing through the correct info now. I found the fix on another PBI post.

= Table.AddColumn(#"Added Conditional Column6", "Status completed & % complete not 100%", each if [Status]="Completed" and [#"% Complete"] <1.0 then "Discrepancy"
else if [Status]="In Progress" and [#"% Complete"]=1.0 then "Discrepancy"
else if [Status]="Overdue" and [#"% Complete"]=1.0 then "Discrepancy"
else if [Status]="Not Started" and [#"% Complete"]=1.0 then "Discrepancy"
else "")