Multiple IF condition requirements are always stuck and confused with some sub-calculations in it

[dinesharivalaga]

Hi Experts,

I am working on a scenario which is having multiple IF conditions and also having some sub-calculations which makes me confused and challenging.

Below the requirement :

If the "Cost Spent YTD" is more than 10% of Planned cost YTD", it should be "Red", if less than 10%, it should be "Amber" otherwise "Green" and
If the "Overall budgeted cost" is less than "Cost Spent YTD"+"What is still needed to complete the project?" by more than 10% then "Red", if it is less than 10% and more than 0%, then "Amber", otherwise "Green" and
If "Is the budget overrun due to customer dependency/CG side?" is "Yes" and Previous 2 IF conditions are "Red", then change the status to "Amber" and
If "Is the CR raised for this additional cost?" is "Yes" then keep the same colour of previous IF condition and
If "Is the CR approved for this additional cost?" is "Yes" then change the colour to "Green", otherwise keep the same colour and
If "Is there a dip in margin?" is less than 0% then change the colour to "Amber" , if it is less than -3% then "Red" otherwise "Green" and
If "is the SOW signed?" is "No" then change the colour to "Amber" if it was "Green", if it is already "Red", keep it "Red" only and
If "Is there any outstanding invoices to be raised?" is "Yes" then change the colour to "Amber" if it was "Green" ,if it is already "Red", keep it "Red" only

It should be a calculated column to add into table visual.

DAX which i have created by using some other references:

Cost Logic =

VAR CostSpentYTD = [Cost spent]

VAR PlannedCostYTD = [Planned cost]

VAR OverallBudgetedCost = [Overall Budgeted Cost]

VAR NeededToComplete = [What is still needed to complete the project?]

VAR CustomerDependency = [Is the budget overrun due to customer dependency/CG side?]

VAR CRRaised = [Is the CR raised for this additional cost?]

VAR CRApproved = [Is the CR approved for this additional cost?]

VAR MarginDip = [Is there a dip in margin?]

VAR SOWSigned = [Is the SOW signed?]

VAR OutstandingInvoices = [Is there any outstanding invoices to be raised?]

VAR InitialStatus =

    SWITCH(

        TRUE(),

        CostSpentYTD > (PlannedCostYTD * 0.1), "Red",

        CostSpentYTD < (PlannedCostYTD * 0.1), "Amber",

        "Green"

    )

VAR BudgetStatus =

    SWITCH(

        TRUE(),

        OverallBudgetedCost < CostSpentYTD + NeededToComplete * 0.9, "Red",

        OverallBudgetedCost < CostSpentYTD + NeededToComplete && OverallBudgetedCost >= CostSpentYTD + NeededToComplete * 0.1, "Amber",

        "Green"

    )

VAR CombinedStatus =

    SWITCH(

        TRUE(),

        CustomerDependency = "CG" && InitialStatus = "Red" && BudgetStatus = "Red", "Amber",

        CRRaised = "Yes", InitialStatus,

        CRApproved = "Yes", "Green",

        InitialStatus

    )

VAR MarginStatus =

    SWITCH(

        TRUE(),

        MarginDip < -3, "Red",

        MarginDip < 0, "Amber",

        "Green"

    )

VAR FinalStatus =

    SWITCH(

        TRUE(),

        SOWSigned = "No" && CombinedStatus = "Green", "Amber",

        SOWSigned = "No" && CombinedStatus = "Red", "Red",

        OutstandingInvoices = "Yes" && CombinedStatus = "Green", "Amber",

        OutstandingInvoices = "Yes" && CombinedStatus = "Red", "Red",

        CombinedStatus

    )

RETURN

    SWITCH(

        TRUE(),

        FinalStatus = "Amber", "Amber",

        FinalStatus = "Red", "Red",

        FinalStatus

    )

If i use this DAX then the accounts are duplicating and resulting all the colours like below :

Based on the data source it should give only "RED" as per below condition

CostSpentYTD > (PlannedCostYTD * 0.1), "Red",

CostSpentYTD < (PlannedCostYTD * 0.1), "Amber",

"Green"

Please help to solve this calculation .

Thanks

DK

[Anonymous]

Hi @dinesharivalaga ,

Your DAX may need to be modified.

The part of the expression in the DAX that corresponds to this scenario should be /0.9/ instead of /0.1/.
If the "Overall budgeted cost" is less than "Cost Spent YTD"+"What is still needed to complete the project?" by more than 10% then "Red", if it is less than 10% and more than 0%, then "Amber", otherwise "Green"

VAR BudgetStatus =

    SWITCH(

        TRUE(),

        OverallBudgetedCost < CostSpentYTD + NeededToComplete * 0.9, "Red",

        OverallBudgetedCost < CostSpentYTD + NeededToComplete && OverallBudgetedCost >= CostSpentYTD + NeededToComplete * 0.9, "Amber",

        "Green"

    )

The part of the expression in the DAX that corresponds to this scenario should be /Yes/ instead of /CG/.
If "Is the budget overrun due to customer dependency/CG side?" is "Yes" and Previous 2 IF conditions are "Red", then change the status to "Amber" 

VAR CombinedStatus =

    SWITCH(

        TRUE(),

        CustomerDependency = "Yes" && InitialStatus = "Red" && BudgetStatus = "Red", "Amber",

        CRRaised = "Yes", InitialStatus,

        CRApproved = "Yes", "Green",

        InitialStatus

    )

If the above do not help, please provide sample data for us to better understand and help you with your problem.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Dengliang Li

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

[dinesharivalaga]

@Anonymous 
Below the table which i am working on it.

Cost logic 1 is a DAX , once it added into the table , the account names were duplicated and resulting all the colours in the Cost logic column. but based on the formula it should give only "RED".

Suspect that the duplication was happened due to the budget status column (Unpivoted column applied) , so that all the values are present as a different rows and given all the results in the cost logic 1. Anyhow we will not use budget status in the table.

DAX formula used as you know :

Cost Logic 1 =

VAR CostSpentYTD = [Cost spent]

VAR PlannedCostYTD = [Planned cost]

VAR OverallBudgetedCost = [Overall Budgeted Cost]

VAR NeededToComplete = [What is still needed to complete the project?]

VAR CustomerDependency = [Is the budget overrun due to customer dependency/CG side?]

VAR CRRaised = [Is the CR raised for this additional cost?]

VAR CRApproved = [Is the CR approved for this additional cost?]

VAR MarginDip = [Is there a dip in margin?]

VAR SOWSigned = [Is the SOW signed?]

VAR OutstandingInvoices = [Is there any outstanding invoices to be raised?]

VAR InitialStatus =

    SWITCH(

        TRUE(),

        (CostSpentYTD) > (PlannedCostYTD * 0.1), "Red",

        (CostSpentYTD) < (PlannedCostYTD * 0.1), "Amber",

        "Green"

    )

VAR BudgetStatus =

    SWITCH(

        TRUE(),

        OverallBudgetedCost < (CostSpentYTD + NeededToComplete) * 0.1, "Red",

        OverallBudgetedCost < (CostSpentYTD + NeededToComplete) && OverallBudgetedCost >= (CostSpentYTD + NeededToComplete) * 0.9, "Amber",

        "Green"

    )

VAR CombinedStatus =

    SWITCH(

        TRUE(),

        CustomerDependency = "CG" && InitialStatus = "Red" && BudgetStatus = "Red", "Amber",

        CRRaised = "Yes", InitialStatus,

        CRApproved = "Yes", "Green",

        InitialStatus

    )

VAR MarginStatus =

    SWITCH(

        TRUE(),

        MarginDip < -3, "Red",

        MarginDip < 0, "Amber",

        "Green"

    )

VAR FinalStatus =

    SWITCH(

        TRUE(),

        SOWSigned = "No" && CombinedStatus = "Green", "Amber",

        SOWSigned = "No" && CombinedStatus = "Red", "Red",

        OutstandingInvoices = "Yes" && CombinedStatus = "Green", "Amber",

        OutstandingInvoices = "Yes" && CombinedStatus = "Red", "Red",

        CombinedStatus

    )

RETURN

    SWITCH(

        TRUE(),

        FinalStatus = "Amber", "Amber",

        FinalStatus = "Red", "Red",

        FinalStatus

    )

Expected result : Account names are just a sample dummy names.

Hope this data is OK for you ..

Thanks

DK

[dinesharivalaga]

@Anonymous  Thanks for your response 🙂

The part of the expression in the DAX that corresponds to this scenario should be /Yes/ instead of /CG/.
If "Is the budget overrun due to customer dependency/CG side?" is "Yes" and Previous 2 IF conditions are "Red", then change the status to "Amber"   --> Sorry it is not Yes/No , actually the choices should be CG / Customer 

The part of the expression in the DAX that corresponds to this scenario should be /0.9/ instead of /0.1/.
If the "Overall budgeted cost" is less than "Cost Spent YTD"+"What is still needed to complete the project?" by more than 10% then "Red", if it is less than 10% and more than 0%, then "Amber", otherwise "Green"  --> Yes I have updated it into 0.9

But my question is about Cost (1st condition) 

Can you please share your feedback on that ?