Challenges with color icon changes in the table when select partitions in pie chart

[dinesharivalaga]

I am struggling to fix like the same interaction issue between pie chart and table .

Here in the table you can see 3 different cost category columns , but only cost dynamic should be kept finally and other 2 will remove , just for reference i have added all the cost category in the table.

you could see the measure for cost dynamic which will work based on the selection in the Offer type slicer (if we select any option from the slicer offer type then the result would be from cost logic otherwise it will show the result from cost combined) , this works fine .

Now i want the same logic should be applied to pie chart (if we select any partition from the pie chart then the result would be from cost logic otherwise it will show the result from cost combined)

Green circle is the expected result when click in pie chart .

Pie chart data is Cost logic

Below the used DAX :

Cost logic =

VAR CostSpentYTD = 'Test Delivery Updates'[Cost Spent YTD]

VAR PlannedCostYTD = 'Test Delivery Updates'[Planned Cost YTD]

VAR OverallBudgetedCost = 'Test Delivery Updates'[Overall Budgeted Cost]

VAR WhatIsStillNeeded = 'Test Delivery Updates'[What is still needed to complete the project?]

VAR IsBudgetOverrun = 'Test Delivery Updates'[Is the budget overrun due to customer dependency/CG side?]

VAR IsCRRaised = 'Test Delivery Updates'[Is the CR raised for this additional cost?]

VAR IsCRApproved = 'Test Delivery Updates'[Is the CR approved for this additional cost?]

VAR DipInMarginText = 'Test Delivery Updates'[Is there a dip in margin?]

VAR DipInMargin = VALUE(SUBSTITUTE(DipInMarginText, "%", ""))  // Convert percentage text to number

VAR IsSOWSigned = 'Test Delivery Updates'[Is the SOW signed?]

VAR OutstandingInvoices = 'Test Delivery Updates'[Is there any outstanding invoices to be raised?]

VAR YTDActualFTE = 'Test Delivery Updates'[YTD Actual FTE Release]

VAR YTDPlanFTE = 'Test Delivery Updates'[YTD Planned FTE Release]

VAR YTDActualprocess = 'Test Delivery Updates'[YTD Actual process deployed count]

VAR YTDPlanprocess = 'Test Delivery Updates'[YTD Planned Process deployed count]

VAR catchup = 'Test Delivery Updates'[Is there a catch up plan available?]

VAR PercentageOver1 = DIVIDE(CostSpentYTD - PlannedCostYTD, PlannedCostYTD, 0)

VAR Status1 =

    IF(

        PercentageOver1 > 0.1, "Red",

        IF(

            PercentageOver1 < 0.1, "Amber",

            "Green"

        )

    )

VAR TotalCostNeeded = CostSpentYTD + WhatIsStillNeeded

VAR PercentageOver2 = DIVIDE(TotalCostNeeded - OverallBudgetedCost, OverallBudgetedCost, 0)

VAR Status2 =

    IF(

        PercentageOver2 > 0.1, "Red",

        IF(

            PercentageOver2 > 0 && PercentageOver2 <= 0.1, "Amber",

            "Green"

        )

    )

VAR Status3 =

    SWITCH(

        TRUE(),

        IsBudgetOverrun = "Customer" && (Status1 = "Red" || Status2 = "Red"), "Amber",

        IsBudgetOverrun = "CG" && (Status1 = "Red" || Status2 = "Red") && IsSOWSigned = BLANK(), "Red",IsBudgetOverrun = "CG" && (Status1 = "Red" || Status2 = "Red") && IsSOWSigned = "No","Amber"

    )

VAR FinalStatus =

    SWITCH(

        TRUE(),

        YTDActualFTE <> BLANK() && YTDPlanFTE <> BLANK(),IF(ABS(YTDActualFTE - YTDPlanFTE) > YTDPlanFTE * 0.1, "Red",IF(ABS(YTDActualFTE - YTDPlanFTE) <= YTDPlanFTE * 0.1 && YTDActualFTE <> YTDPlanFTE , "Amber",IF(YTDActualFTE = YTDPlanFTE, "Green"))),

        YTDActualprocess <> BLANK() && YTDPlanprocess <> BLANK(),IF(ABS(YTDActualprocess - YTDPlanprocess) > YTDPlanprocess * 0.1 && catchup = BLANK(), "Red",IF(ABS(YTDActualprocess - YTDPlanprocess) <= YTDPlanprocess * 0.1 && YTDActualprocess <> YTDPlanprocess, "Amber",IF(YTDActualprocess = YTDPlanprocess, "Green",IF(catchup = "Yes","Amber",IF(catchup = "No","Red"))))),

        DipInMargin <> BLANK() && IsSOWSigned <> BLANK() && OutstandingInvoices <> BLANK(),IF(DipInMargin  < -3, "Red",IF(DipInMargin < 0,"Amber",IF(DipInMargin >= 0 && IsSOWSigned = BLANK() && OutstandingInvoices = BLANK(),"Green",IF(IsSOWSigned = "No" && OutstandingInvoices = BLANK(), "Amber",IF(OutstandingInvoices = "Yes", "Amber",IF(OutstandingInvoices = "No","Green")))))),

        IsCRRaised = "Yes" && (Status1 = "Red" || Status2 = "Red") , Status3,

        IsCRApproved = "Yes", "Green",

        IsBudgetOverrun = "Customer" && IsCRRaised = "No" && IsCRApproved = "No", "Red",

        IsBudgetOverrun = "Customer" && (Status1 = "Red" || Status2 = "Red"), "Amber",

        IsBudgetOverrun = "CG" && (Status1 = "Red" || Status2 = "Red"), "Red",

        "Green"

    )

RETURN

    FinalStatus

Cost Combined =  

VAR A = 'Test Delivery Updates'[Account Name]

VAR B = ADDCOLUMNS(SUMMARIZE(FILTER('Test Delivery Updates','Test Delivery Updates'[Account Name]=A),'Test Delivery Updates'[Cost logic]),"Cost",SWITCH('Test Delivery Updates'[Cost logic],"Red",3,"Amber",2,"Green",1,0))

RETURN MAXX(TOPN(1,B,[Cost]),'Test Delivery Updates'[Cost logic])

Cost Dynamic =

IF(

    ISFILTERED('Test Delivery Updates'[Offer Type]) && HASONEVALUE('Test Delivery Updates'[Offer Type]),

    MAX('Test Delivery Updates'[Cost logic]),

    MAX('Test Delivery Updates'[Cost Combined])

)

There is no issue with any DAX functions , all the results are OK but we need to work on the interaction between Pie chart and Table.
Cost Combined is used to combine the icon colors when 2 same accounts created based on the higher risk (if one account is Red , other account is Green , then Cost Combined result will be "RED" )

Please help to achieve this one ..

Thanks

DK

[VahidDM · Accepted solution]

Hi @dinesharivalaga 

Adjust your Cost Dynamic measure to consider the pie chart’s selection in addition to the slicer selection. Right now, it only checks if an Offer Type is filtered. Extend that logic to also check if the pie chart’s field is being filtered. For example, if your pie chart is based on Category:

Cost Dynamic =
IF(
    (
        ISFILTERED('Test Delivery Updates'[Offer Type]) && HASONEVALUE('Test Delivery Updates'[Offer Type])
    )
    ||
    (
        ISFILTERED('Test Delivery Updates'[Category]) && HASONEVALUE('Test Delivery Updates'[Category])
    ),
    MAX('Test Delivery Updates'[Cost logic]),
    MAX('Test Delivery Updates'[Cost Combined])
)

Replace [Category] with the actual field used in your pie chart. This ensures that when a user selects a slice in the pie chart, the measure switches to use Cost logic, just like it does when a single Offer Type is selected.

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[dinesharivalaga]

@VahidDM  Cool , it works well ..