Forum Discussion
Table is not interacted properly when select any pie chart slices
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.
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 Yes this works , and one more thing is that the slicer order is changed ..
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 Cool it works well now. Thanks a lot 🙂
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 numberVAR 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 + WhatIsStillNeededVAR 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")RETURNFinalStatusCost 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 ..