Forum Discussion
Conditional formatting issue using percentages
Hi, dreader
You can try the following methods.
ExpectedLevels = DATATABLE( "Prospect Status", STRING, "Expected Value", DOUBLE, { {"2. Qualification", 0.15}, {"3. Cultivation", 0.45}, {"4. Solicitation", 0.25}, {"5. Stewardship", 0.15} } )Column:
Expect = LOOKUPVALUE(ExpectedLevels[Expected Value],ExpectedLevels[Prospect Status],[Status])Color Measure = SWITCH(TRUE(), SUM('Table'[Value])>SUM('Table'[Expect])+0.1,"Red", SUM('Table'[Value])<SUM('Table'[Expect])-0.1,"Red")Result:
Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous , Thank you for your suggestion. This is very helpful and I think it might work. However, I need a little extra help in creating the Value measure or column. I'm getting that value visually by applying the Show value as Percent of Row Total to the Count of Status field. Thanks!
Hi dreader ,
The issue with your matrix visual may come from filter context or data mismatches. Ensure CnPr[CnPr_Status] is filtered to a single value; otherwise, SELECTEDVALUE may return BLANK(). Replace it with HASONEVALUE for reliability. Verify that CnPr[CnPr_Status] matches ExpectedLevels[Prospect Status] exactly, or standardize using TRIM and UPPER. Include error handling in the Color Status measure to avoid issues when PercentageValue or ExpectedLevel is BLANK(). Lastly, check your conditional formatting settings to confirm the Color Status measure is applied.
DAX Formulas:
Color Status:
Color Status =
VAR SelectedStatus =
IF(
HASONEVALUE(CnPr[CnPr_Status]),
VALUES(CnPr[CnPr_Status]),
BLANK()
)
VAR ExpectedLevel =
LOOKUPVALUE(
ExpectedLevels[Expected Value],
ExpectedLevels[Prospect Status],
TRIM(UPPER(SelectedStatus))
)
VAR PercentageValue = [Percentage of Status]
RETURN
IF(
NOT ISBLANK(PercentageValue) && NOT ISBLANK(ExpectedLevel) &&
ABS(PercentageValue - ExpectedLevel) >= 10,
"#FF0000", // Red for deviations of 10 percentage points or more
BLANK() // No color formatting for other cases
)
Percentage of Status:
Percentage of Status =
DIVIDE(
COUNT(CnPr[CnPr_Status]),
CALCULATE(COUNT(CnPr[CnPr_Status]), ALLSELECTED(CnRelSol_1[CnRelSol_1_Name]))
) * 100
These updates should address any discrepancies in your matrix visual. Let me know if you need further clarification!
Best regards,
Hi DataNinja777 , Thanks for your detailed response! Much appreciated. I implemented your suggestions however I'm now getting more figures that should be in red and vice versa. I've highlighted in attached screenshot which ones are not correct. Any idea why I would get these results or any other suggestions I can try?
Hi, dreader
You can try the following methods.
ExpectedLevels = DATATABLE( "Prospect Status", STRING, "Expected Value", DOUBLE, { {"2. Qualification", 0.15}, {"3. Cultivation", 0.45}, {"4. Solicitation", 0.25}, {"5. Stewardship", 0.15} } )Column:
Expect = LOOKUPVALUE(ExpectedLevels[Expected Value],ExpectedLevels[Prospect Status],[Status])Color Measure = SWITCH(TRUE(), SUM('Table'[Value])>SUM('Table'[Expect])+0.1,"Red", SUM('Table'[Value])<SUM('Table'[Expect])-0.1,"Red")Result:
Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous , Thank you for your suggestion. This is very helpful and I think it might work. However, I need a little extra help in creating the Value measure or column. I'm getting that value visually by applying the Show value as Percent of Row Total to the Count of Status field. Thanks!