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.

spandy34's avatar
spandy34
Responsive Resident
2 years ago

Hi

I put the code you suggested into a custom column and didnt get any results so I changed equals to, to contains as below.  This is still not working as I am only getting 10 and 20 in all of the columns.  The column is not taking into account the remaining columns.  

 

 

 

Maybe I need the column to say 

 

if [Foundation Status] contains "Complete" then 20 else
if [Foundation Status] contains "In Progress" then 10 else
or

if [Foundation Status] contains "Complete"
&& [Discovery & Design Status] = "Complete" then 40 else
or
if [Foundation Status] contains "Complete"
&& [Discovery & Design Status] = "In Progress" then 30 else
or
if [Foundation Status] contains "Complete"
&& Discovery & Design Status] = "Complete" then 40 else
or
if[Foundation Status] contains "Complete"
&& [Discovery & Design Status] = "Complete"
&& [Planning Status] = "Complete" then 60 else
or
if[Foundation Status] contains "Complete"
&& [Discovery & Design Status] = "Complete"
&& [Planning Status] = "In Progress" then 50 else
0

 

Can I have your opinion please?

  • spandy34's avatar
    spandy34
    Responsive Resident
    2 years ago
    Mission Project ID Project Project Checklist Foundation Status Foundation Gateway Approved at Mission Board Foundation Gateway Approved at CMT PoF Board Discovery & Design Status Discovery & Design Gateway Approved at Mission Board Discovery & Design Gatway Approved at CMT PoF Board Planning Status Planning  Gateway Approved at Mission Board Planning  Gateway Approved at CMT PoF Board Delivery Status Delivery Gateway Approved at Mission Board Delivery Gateway Approved at CMT PoF Board Closure Status Closure Gateway Approved at Mission Board Closure Gateway  Approved at CMT PoF Board RAIDL Project  Delivery Plan Comms Plan Risk Assessment Interdependancies mappedTOM Capital ReqStart Date End Date Current Gateway Status Potential Budget Reduction 24/25Potential Budget Reduction 25/26Potential Budget Reduction 26/27PID Options Appraisal RAIDL _1Project Delivery Plan Benefits Card IndexUnique LinkMission Project% Progress
    TransformationMT01Waste Collection Complete18/10/202324/10/2023In Progress######## In Progress  Not Started  Not Started  In ProgressIn Progress  In Progress Yes########31/08/2026GW2 - Discovery & DesignOn Schedule£12.00£13.00£14.00Waste PIDWaste Options AppraisalWaste RAIDL.xlsx (sharepoint.com)Waste Project Plan 11 Transformation MT01 Waste CollectionTransformation - Waste Collection 
    TransformationMT02Fleet Management Complete16/11/202321/11/2023In Progress######## In Progress  Not Started  Not Started  RAIDL not updatedIn Progress  In Progress  ########01/02/2025GW2 - Discovery & DesignOn Schedule   Fleet PIDNot Completed YetFleet RAIDLFleet Project Plan 22 Transformation MT02 Fleet ManagementTransformation - Fleet Management 
    TransformationMT03Supporting people to live independently Complete15/02/202428/02/2024In Progress  In Progress  Not Started  Not Started  In ProgressIn Progress  In Progress  ########01/12/2024GW1 - FoundationOn Schedule   Intermediate Care PIDNot Completed YetIntermediate Care RAIDLIntermediate Care Project Plan 33 Transformation MT03 Supporting people to live independentlyTransformation - Supporting people to live independently 
    TransformationMT12Fees & Charges Complete25/03/202408/04/2024In Progress  In Progress  Not Started  Not Started  RAIDL not updated   In Progress  24-Jan-2401/04/2025GW1 - FoundationOn Schedule   Fees & Charges PIDNot Completed YetNot MaintainedNot Completed Yet 1212 Transformation MT12 Fees & ChargesTransformation - Fees & Charges 
    TransformationMT13Children in Our Care Complete15/02/202428/02/2024In Progress  In Progress  In Progress  Not Started  In ProgressIn ProgressIn ProgressCompleteIn Progress Yes########01/04/2026GW2 - Discovery & DesignBehind Schedule   CIOC PIDNot Completed YetCIOC RAIDLCIOC Project Plan 1313 Transformation MT13 Children in Our CareTransformation - Children in Our Care 
    TransformationMT14SEND/High Needs Complete15/02/202428/02/2024In Progress  In Progress  In Progress  Not Started  In ProgressIn ProgressNot StartedIn ProgressIn Progress Yes########01/09/2024GW2 - Discovery & DesignOn Schedule   SEND/High Needs PIDSEND Options AppraisalSEND RAIDLSEND Project Plan 1414 Transformation MT14 SEND/High NeedsTransformation - SEND/High Needs 
    TransformationMT15Cashiers CompleteN/AN/AComplete  In Progress  Complete  In Progress  CompleteCompleteCompleteCompleteIn Progress  ########01/09/2023GW5 - ReviewOn Schedule   Cashiers PIDCashiers Financial AppraisalN/AN/A 1515 Transformation MT15 CashiersTransformation - Cashiers 
    CommunitiesMCOM01Communications and Engagement In Progress                     01-Jan-24 GW1 - FoundationOn Schedule        1616 Communities  MCOM01 Communications and EngagementCommunities  - Communications and Engagement 
    CommunitiesMCOM02Community Engagement In Progress                     01-Jan-24 GW1 - FoundationOn Schedule        1717 Communities  MCOM02 Community EngagementCommunities  - Community Engagement 
    CommunitiesMCOM03Community Development In Progress                     01-Jan-24 GW1 - FoundationOn Schedule        1818 Communities  MCOM03 Community DevelopmentCommunities  - Community Development 
    ColleaguesMC01Organisational Culture In Progress04/04/202425/04/2024 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationOn Schedule   Organisational Culture PIDN/AOrg Culture RAIDLNot Completed Yet 1919 Colleagues  MC01  Organisational CultureColleagues  - Organisational Culture 
    ColleaguesMC02Communication & Engagement In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationOn Schedule   Comms & Engagement PIDN/AComms & Engagement RAIDLNot Completed Yet 2020 Colleagues  MC02 Communication & EngagementColleagues  - Communication & Engagement 
    ColleaguesMC03Smarter Working In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW4 - DeliveryOn Schedule   Smarter Working PIDN/ASmarter Working RAIDLNot Completed Yet 2121 Colleagues  MC03 Smarter WorkingColleagues  - Smarter Working 
    ColleaguesMC04Dunedin/Relocation In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/08/2024GW4 - DeliveryOn Schedule   N/AN/ADunedin RAIDLDunedin Project Plan 2222 Colleagues  MC04 Dunedin/RelocationColleagues  - Dunedin/Relocation 
    ColleaguesMC05Attract & Retain In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationBehind Schedule   Attract & Retain PIDN/AAttract & Retain RAIDLAttract & Retain Project Plan 2323 Colleagues  MC05 Attract & RetainColleagues  - Attract & Retain 
    ColleaguesMC06Happy and Healthy workforce In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationOn Schedule   Happy & Healthy WorkforceN/AHappy & Healthy Workforce RAIDLNot Completed Yet 2424 Colleagues  MC06 Happy and Healthy workforceColleagues  - Happy and Healthy workforce 
    ColleaguesMC07Workforce Planning In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationBehind Schedule   Workforce Planning PIDN/AWorkforce Planning RAIDLNot Completed Yet 2525 Colleagues  MC07 Workforce PlanningColleagues  - Workforce Planning 
    ColleaguesMC08Workforce Development In Progress04/04/202425/04/24 TBCIn Progress                  01-Jan-2401/06/2025GW1 - FoundationBehind Schedule   Workforce Development PIDN/AWorkforce Development RAIDLNot Completed Yet 2626 Colleagues  MC08 Workforce DevelopmentColleagues  - Workforce Development 
    PartnershipsMPar01Charter In Progress 08/04/2024                   ######## GW1 - FoundationOn Schedule   Partnership Mission CharterN/APartnerships Mission RAIDLNot Completed Yet 2727 Partnerships  MPar01 CharterPartnerships  - Charter 
    PartnershipsMPar02Team Stockton In Progress                     ######## GW1 - FoundationOn Schedule        2828 Partnerships  MPar02 Team StocktonPartnerships  - Team Stockton 
    PartnershipsMPar03Bilateral Partnerships In Progress                     ######## GW1 - FoundationOn Schedule        2929 Partnerships  MPar03 Bilateral PartnershipsPartnerships  - Bilateral Partnerships 
    PartnershipsMPar04SBC/DBC Shared Services Offer In Progress                     ######## GW1 - FoundationOn Schedule        3030 Partnerships  MPar04 SBC/DBC Shared Services OfferPartnerships  - SBC/DBC Shared Services Offer 
    PartnershipsMPar05Business Ambassadors In Progress                     ######## GW1 - FoundationOn Schedule        3131 Partnerships  MPar05 Business AmbassadorsPartnerships  - Business Ambassadors 
    • 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.