Align the table visual as single line with all the values

[dinesharivalaga]

Hi Experts,

I want to get the result based on the cost alloted to the accounts.

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 row and given all the results in the cost logic 1. Anyhow we will not use budget status in the table.

formula is below :

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.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

    )

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

Please help to fix this guys.

Thanks

DK

[v-tangjie-msft · Accepted solution]

Hi @dinesharivalaga ,

Can you try to create measure?

Cost Logic 1 =
VAR CostSpentYTD = MAX('Table'[Cost spent])
VAR PlannedCostYTD = MAX('Table'[Planned cost])
VAR OverallBudgetedCost = MAX('Table'[Overall Budgeted Cost])
VAR NeededToComplete = MAX('Table'[What is still needed to complete the project?])
VAR CustomerDependency = MAX('Table'[Is the budget overrun due to customer dependency/CG side?])
VAR CRRaised = MAX('Table'[Is the CR raised for this additional cost?])
VAR CRApproved = MAX('Table'[Is the CR approved for this additional cost?])
VAR MarginDip = MAX('Table'[Is there a dip in margin?])
VAR SOWSigned = MAX('Table'[Is the SOW signed?])
VAR OutstandingInvoices = MAX('Table'[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.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 the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[dinesharivalaga]

@v-tangjie-msft  Thanks Neeko,

Received this error message :

"The MAX function only accepts a column reference as the argument number 1."

FYI :

1st 3 variable are measures which is created in the table, rest all variable is the column names from the data source.

Cost Logic 1 =
VAR CostSpentYTD = MAX('Table'[Cost spent])
VAR PlannedCostYTD = MAX('Table'[Planned cost])
VAR OverallBudgetedCost = MAX('Table'[Overall Budgeted Cost])

[dinesharivalaga]

@v-tangjie-msft 

I have removed MAX from the above 3 measures and I can see the below table now ("Green" converted into "Amber")

[dinesharivalaga]

@v-tangjie-msft  It is solved now .

I have created the same as a measure and it worked .