Conditional formatting issue using percentages

[dreader]

Fundraising staff at my organization are required to maintain a balanced portfolio of prospects based on set percentages for each status level. Here are those levels and percentanges:

Qualification 15%

Cultivation 45%

Solicitation 25%

Stewardship 15%

When they are 10% below or above any expected level I want the percentage to change to a red font color in my matrix visual.

I have set this up with the following table and measures and it seems to be working for the most part. However there are figures that should be red but are not, and vice versa.

Any help in resolving this issue would be greatly appreciated. Thanks!

ExpectedLevels = DATATABLE(

    "Prospect Status", STRING,

    "Expected Value", DOUBLE,

    {

        {"2. Qualification", 15.0},

        {"3. Cultivation", 45.0},

        {"4. Solicitation", 25.0},

        {"5. Stewardship", 15.0}

    }

)

Color Status =

VAR SelectedStatus = SELECTEDVALUE(CnPr[CnPr_Status])

VAR ExpectedLevel = LOOKUPVALUE(ExpectedLevels[Expected Value], ExpectedLevels[Prospect Status], SelectedStatus)

VAR PercentageValue = [Percentage of Status]

RETURN

    IF(

        ABS(PercentageValue - ExpectedLevel) >= 10,

        "#FF0000", // Red for deviations of 10 percentage points or more

        BLANK()    // No color formatting for other cases

    )

Percentage of Status =

DIVIDE(

    COUNT(CnPr[CnPr_Status]),

    CALCULATE(COUNT(CnPr[CnPr_Status]), ALLEXCEPT(CnRelSol_1, CnRelSol_1[CnRelSol_1_Name]))

) * 100

[Anonymous · Accepted solution]

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.

[DataNinja777]

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,

[dreader]

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? 

[Anonymous · Accepted solution]

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.

[dreader · Accepted solution]

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!

[tharunkumarRTK]

@dreader 

For dubugging, create a matrix visual and add your [Color Status] measure into it. If you observe any mistake in the returned results, continue investigating each and every variable and apply the fix if required.

Please be informed,  for percentage meausres,  the division value  would be between 0 to 1. Applying percentage format string will multiply it with 100 and the '%' to the value. I am not sure which meausre you added to your visual, I could see that it has percentage symbol, so I am assuming you applied percentage format string. I also observed that your percentage measure has a multiplication factor of 100. Just check if there is anything you missed here

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!