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 

 

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

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