Table is not interacted properly when select any pie chart slices

[Anonymous]

Hi Experts,

Table is not highlighting properly when i selected 40-50% range in pie chart.

All data from the same table data only.

Below the DAX used in pie chart ranges:

CM % split = IF('Test Delivery Updates'[CM %]<0.30,"<30%",IF('Test Delivery Updates'[CM %]>=0.30 && 'Test Delivery Updates'[CM %]<=0.40,"30-40%",IF('Test Delivery Updates'[CM %]>=0.40 && 'Test Delivery Updates'[CM %]<=0.50,"40-50%",IF('Test Delivery Updates'[CM %]>0.50,">50%"))))

Please help to sort this ..

Thanks

DK

[AnkitKukreja]

Hi! @Anonymous 

Please check edit interactions property in case that is changed.

[Anonymous]

Hi @Anonymous ,

I did not reproduce your problem in my testing.

Below is the test data table.

After selecting a slice of the pie chart, the table visual generated a corresponding interaction.

Please check if the pie chart has no impact on the table visual due to editing interactions.

Please see the attached pbix for reference.

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.

[Anonymous]

@Anonymous  @AnkitKukreja  Thanks for your responses 🙂

Interaction is good only so that the result is coming but instead of filtering the exact rows it showcasing all the rows and highlighting only the rows based on the pie chart selections.

Also CM% in the table is not a direct value from the table , that is also a measure :

CM % = CALCULATE(DIVIDE([Total Actual Revenue],[Total YTD Revenue],0),ALLEXCEPT('Test Delivery Updates','Test Delivery Updates'[Account Name]))

[Anonymous]

Hi @Anonymous ,

Please refers to the following steps.

Create a calculated table derived from the 'CM % split' column.
And use the columns in this table to create a pie chart.

Table = SELECTCOLUMNS('Test Delivery Updates',"CM % split",'Test Delivery Updates'[CM % split])

Create a measure is used to set the conditional format for the 'CM%' measure.

Color = 
VAR SelectedSlice= IF(DISTINCTCOUNT('Table'[CM % split])=1,MAX('Table'[CM % split]))
VAR _res=
SWITCH( TRUE(),
        [CM %]<0.3,"<30%",
        [CM %]<=0.4,"30-40%",
        [CM %]<=0.5,"40-50%",
        [CM %]>0.5, ">50%")
RETURN
IF(_res=SelectedSlice,
SWITCH(_res,
    "<30%","Green",
    "30-40%","Red",
    "40-50%","Yellow",
    ">50%","Brown")
)

The final result is as follows.

Please see the attached pbix for reference.

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.

[Anonymous]

@Anonymous  Yes this also recommeneded when we have very less no. of accounts in the table to highlight color.

But in my dashboard we have more than 150+ accounts (rows) so still we have to scroll down to see the color highlighting rows. 

It would be a manual thing again for the stalkholders to scroll until the end to see the results.

[Anonymous]

Hi @Anonymous ,

In that case, then you can create a pie chart using the CM % split column in the source table Test Delivery Updates.
Then set the conditional format of [CM %] using the following measure.

Color2 = 
VAR SelectedSlice = CALCULATE(
    IF(DISTINCTCOUNT('Test Delivery Updates'[CM % split])=1,MAX('Test Delivery Updates'[CM % split])),
    ALLSELECTED('Test Delivery Updates'[Account Name],'Test Delivery Updates'[YTD Revenue])
    )
VAR _res=
SWITCH( TRUE(),
        [CM %]<0.3,"<30%",
        [CM %]<=0.4,"30-40%",
        [CM %]<=0.5,"40-50%",
        [CM %]>0.5, ">50%")
RETURN
IF(_res=SelectedSlice,
SWITCH(_res,
    "<30%","Green",
    "30-40%","Red",
    "40-50%","Yellow",
    ">50%","Brown")
)

This way, when you select any slice of the pie chart, the corresponding row is highlighted in the table visual. When you deselect the slice, all rows are displayed in the table visual.

Please see the attached pbix for reference.

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.

[Anonymous]

@Anonymous  Yes this works , and one more thing is that the slicer order is changed ..

[Anonymous · Accepted solution]

Hi @Anonymous ,

Creates a measure to be used for sorting.

Sort = SWITCH(
MAX('Test Delivery Updates'[CM % split]),
"<30%",1,
"30-40%",2,
"40-50%",3,
">50%",4
)

Then add this measure to the tooltip field of the pie chart.
The legend of the pie chart is reordered by Sort By measure.

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.

[Anonymous]

@Anonymous  Hey One more thing 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 ..

[Anonymous]

@Anonymous  Cool it works well now. Thanks a lot 🙂