Forum Discussion

siddrow's avatar
siddrow
Icon for Helper III rankHelper III
4 years ago
Solved

How to do <100% in custom column

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 %...
Need Help
  • siddrow's avatar
    siddrow
    4 years ago

    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 "")

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
4 years ago

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's avatar
siddrow
Icon for Helper III rankHelper III
4 years ago

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 "")