Forum Discussion

spandy34's avatar
spandy34
Responsive Resident
2 years ago
Solved

Creating a Column Multiple Criteria

I have a table called RAG Monitoring that monitors the progress of projects.  I want to create a column called % Progress .  I don’t know how to represent what I want but looking at the abo...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi spandy34 

     

    If you click the custom column, you can paste this code: 

    if [#"Closure Status "] = "Complete" then 100 else if [#"Closure Status "] = "In Progress" then 90 else if [#"Delivery Status "] = "Complete" then 80 else if [#"Delivery Status "] = "In Progress" then 70 else if [#"Planning Status "] = "Complete" then 60 else if [#"Planning Status "] = "In Progress" then 50 else if [#"Discovery & Design Status "] = "Complete" then 40 else if [#"Discovery & Design Status "] = "In Progress" then 30 else if [#"Foundation Status "] = "Complete" then 20 else if [#"Foundation Status "] = "In Progress" then 10 else 0)

    Here's the PBIX file for your reference.

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi spandy34 

 

Please try this:

In my previous post, I reversed the order:

= Table.AddColumn(#"Changed Type", "Custom", each if [#"Closure Status "] = "Complete" then 100 else if [#"Closure Status "] = "In Progress" then 90 else if [#"Delivery Status "] = "Complete" then 80 else if [#"Delivery Status "] = "In Progress" then 70 else if [#"Planning Status "] = "Complete" then 60 else if [#"Planning Status "] = "In Progress" then 50 else if [#"Discovery & Design Status "] = "Complete" then 40 else if [#"Discovery & Design Status "] = "In Progress" then 30 else if [#"Foundation Status "] = "Complete" then 20 else if [#"Foundation Status "] = "In Progress" then 10 else 0)

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

spandy34's avatar
spandy34
Responsive Resident
2 years ago

I created a new custom column and pasted in the formula you included.  I

 

 

 

I selected Okay and then got the column below

I expanded the Table Icon deselected all the colums apart from the Custom column listed at the bottom

 

 

 

I selected Ok and then got the following – The values are either 0 or 90

 

 

 

My column has different values than yours yet I used the formula you suggested.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi spandy34 

     

    If you click the custom column, you can paste this code: 

    if [#"Closure Status "] = "Complete" then 100 else if [#"Closure Status "] = "In Progress" then 90 else if [#"Delivery Status "] = "Complete" then 80 else if [#"Delivery Status "] = "In Progress" then 70 else if [#"Planning Status "] = "Complete" then 60 else if [#"Planning Status "] = "In Progress" then 50 else if [#"Discovery & Design Status "] = "Complete" then 40 else if [#"Discovery & Design Status "] = "In Progress" then 30 else if [#"Foundation Status "] = "Complete" then 20 else if [#"Foundation Status "] = "In Progress" then 10 else 0)

    Here's the PBIX file for your reference.

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • spandy34's avatar
      spandy34
      Responsive Resident
      2 years ago

      Thats great thank you😀