Conditional Formatting not working in Matrix Visual

[ArchStanton]

Hi,

I have duplicated a Matrix visual so that a COUNT and % are displayed in each visual (I selected the option 'show values as % of Row Total')

The values are just based on this simple measure:

AppValCount = COUNTA('Cases'[applicationvalidationdate])

I'm trying to format the values only in 0-1mth % column, the values here will always be 70-80-90%, I am not interested in the 1-2mth, 23-3mths & Older columns.

Why is it that the following doesn't do anything to the values?

Any help or guidance would be much appreciated!

[Nabha-Ahmed]

hi   

The reason conditional formatting isn’t working in your matrix is because Power BI applies conditional formatting based on the field values, not on a calculated “% of Row Total” display directly. When you use “Show values as % of Row Total,” the formatting rules might not detect the underlying numbers correctly.

Here’s how to fix it:

1. Create a separate measure for the column you want to format (0–1mth %) that calculates the actual percentage:

   Pct_0_1mth = 
   DIVIDE(
   COUNTROWS(FILTER('Cases', [ColumnCondition] = "0-1mth")),
   COUNTROWS('Cases')
   )

   1. Use this measure in your matrix visual instead of relying on “Show values as % of Row Total.”

   2. Apply conditional formatting on this new measure.

       This ensures the conditional formatting rules are applied to the actual values you want, without affecting other columns.

      If this was helpful, don’t forget to mark it as the solution and give a kudo!

      Nabha Ahmed

@ArchStanton

[ArchStanton]

Hi,
I tried your code and it gives me the following result:

Pct_0_1mth = 
VAR CasesValidated = [AppValCount]
RETURN
DIVIDE(
    COUNTROWS(
        FILTER('Cases','Cases'[Days to Validation Bins] = "0-1 Mth")),
        CasesValidated)

I want to see all of the Month 'Bins' and I only want to format values over say 50%. the values i the other bins will never go over 10%-20% so is it possible to format anything >=90% as Green,
>= 20% + <90% Red
< 20% (no formatting so just regular Black font?

[DataNinja777]

Hi @ArchStanton ,

Your conditional formatting isn't working because it's evaluating the underlying raw count from your AppValCount measure, not the displayed percentage. The "Show values as % of Row Total" option is a visual-level setting that doesn't change the data the formatting rule actually sees. So, when your rule checks the value for April in the "0-1 Mth" column, it's testing the number 431 against your conditions (e.g., is 431 between 0.7 and 0.9?). Since it's not, no format is applied.

The solution is to create a new DAX measure that explicitly calculates the percentage value for each cell. You can then base your formatting rules on this new measure. First, create a measure to calculate the percent of the row total by using the following DAX code. Remember to replace 'Cases'[Mth] with the actual name of your month column.

AppVal Percent of Row = 
VAR CurrentValue = [AppValCount]
VAR TotalForRow = CALCULATE(
    [AppValCount],
    ALLEXCEPT('Cases', 'Cases'[Mth])
)
RETURN
    DIVIDE(CurrentValue, TotalForRow)

This DAX formula works by first capturing the value of the current cell (CurrentValue) and then calculating the total for the entire row (TotalForRow) by using ALLEXCEPT to remove all table filters except for the one on the month. It then safely divides the two to get the percentage. After creating this measure, select it and use the Measure tools tab to format it as a Percentage.

Now you can apply the conditional formatting correctly. Select your matrix, navigate to Format your visual > Cell elements, and turn on Background color. In the settings, base the rule on your new AppVal Percent of Row measure. Critically, you must set up your rules using Number as the type, not Percent. Your rules should be: If value is >= 0.9 Number, If value is >= 0.7 Number, etc. This will ensure the formatting evaluates the actual percentage value (e.g., 0.94) correctly.

To address your specific request to format only the "0-1 Mth" column, you'll need one final, specialized measure. This measure will check which column is being evaluated and only return the percentage for the target column, leaving the others blank so they don't get formatted. Be sure to replace 'YourTable'[AgeBandColumn] with the name of the column that contains your aging categories ("0-1 Mth", "1-2 Mths", etc.).

Format 0-1 Mth Column Only = 
IF(
    SELECTEDVALUE('YourTable'[AgeBandColumn]) = "0-1 Mth",
    [AppVal Percent of Row]
)

Finally, go back into the conditional formatting settings one last time and change the field you are basing the rules on to this new Format 0-1 Mth Column Only measure. The rules themselves can stay the same. This will now apply your color logic exclusively to the "0-1 Mth" column.

Best regards,

[ArchStanton]

Using your measure I get the following incorrect result:

AppVal Percent of Row = 
VAR CurrentValue = [AppValCount]
VAR TotalforRow = 
    CALCULATE([AppValCount],
    ALLEXCEPT('AppValTable',AppValTable[Mth ])
    )
RETURN
    DIVIDE(CurrentValue, TotalforRow)

[ArchStanton]

Thank you for your detailed response!
Something isn't working with your suggestion, the percentages are all wrong, there should be 2549 (91.1% see above) in the 0-1 Mth column in the Matrix.

I think the issue is in the ALLEXCEPT part of the code. Also, I don't need a specialised measure to just look at the 0-1 Match category, instead, I could format anything below e.g 25% to remain in Black font so then I would have values between 25% to 89% in Red, and >=90% in Green

I'm not 100% sure about the ALLEXCEPT Function - the date bins (0-1mth etc) are a calculated column called

'Cases' Days to Validation Bins =

Days to Validation Bins = 
                   IF(AND('Cases'[Created On] < DATEVALUE("01/04/2020"), 'Cases'[statecode] = "Resolved" || "2"),BLANK(),
                       IF(ISBLANK('Cases'[Days to Validation]),BLANK(),
                           IF( 'Cases'[Days to Validation] <= 30 , "0-1 Mth",
                               IF('Cases'[Days to Validation] <= 60, "1-2 Mths",
                                   IF('Cases'[Days to Validation] <= 90, "2-3 Mths", 
                                        "Older")))))

The bins are based on this calculated column:

Days to Validation = IF('Cases'[applicationvalidationdate] < 'Cases'[Created On], BLANK(),
                        IF(ISBLANK('Cases'[applicationvalidationdate]),BLANK(),
                        IF(DATEDIFF('Cases'[Created On],'Cases'[applicationvalidationdate],DAY) = 0,
                            (DATEDIFF('Cases'[Created On],'Cases'[applicationvalidationdate],DAY) +1),
                                (DATEDIFF('Cases'[Created On],'Cases'[applicationvalidationdate],DAY)))))

These Bins all live in Cases fact table = 0-1 Mth, 1-2 Mths, 2-3 Mths, Older

I have a second Calendar table called AppValTable which has a One to Many relationship with the Application Validation Date column in the 'Cases' table. The matrix tables I've dispayed are based on the Mth column from this 2nd Calendar,

Its complicated and I wish I was proficient enough in DAX to simplify all this.

What should I be using in the ALLEXCEPT function? 
Do you think this alone will fix it?

Thanks!

[danextian]

Hi @ArchStanton 

Create a measure to be used as the field value in conditional formatting:

Conditional Formatting Color =
VAR _pct = [percentage measure]
RETURN
    IF (
        'table'[column category] = "0-1 Mth",
        SWITCH (
            TRUE (),
            _pct >= 0.9, "green or a hexadecimal value",
            _pct >= 0.9, "red or a hexadecimal value",
            _pct >= 0, "black"
        )
    )

From Format Style, select Field Value and then select the measure above from the dropdown.

[ArchStanton]

Hi, thanks for your reply.

I can't get this to work. I have a calculated column which counts the number of cases that are in the following bins:

0-1 Mth

1-2 Mths

2-3 Mths

Older

This Calculated Column is located in my main Fact table 'Cases'[Days to Validation Bins]

I have created a % Measure for 0-1 Mth only=

1 Mth % = 
VAR UnderOneMth = [0-1 Mth Count]
VAR AllBinAges = [ALL Count]
RETURN
DIVIDE(UnderOneMth,AllBinAges)

0-1 Mth Count Measure =

0-1 Mth Count = 
    CALCULATE(
        [AppValCount],
        KEEPFILTERS(
        'Cases'[Days to Validation Bins] = "0-1 Mth"))

All Count Measure

All Count = 
    CALCULATE(
        [AppValCount])

I'm not really sure what to do with this, I've replied to another expert so maybe what I have said there may help?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[ArchStanton · Accepted solution]

A big thanks to everyone who has offered me help with this - thanks to your input I managed to find a way of solving my issue using the following DAX:

Percent of Row = 
DIVIDE(
    COUNT('Cases'[applicationvalidationdate]),
    CALCULATE(
        COUNT('Cases'[applicationvalidationdate]),
        REMOVEFILTERS('Cases'[Days to Validation Bins])
    )
)

I then applied the following Conditiional Formatting criteria to the percentages (using numbers as advised).

Which gives me the desrired result in my Matrix visual:

[ArchStanton]

Not possible due to data privacy I'm afraid

[danextian]

Create a copy of that report with confidential data anonymized or removed. We don't need the actual pbix.

[ArchStanton]

I have fixed the issue now so I don't need any more help - I have explained how in the replies.

FYI: my report is connected to a live Dynamics database so this isn't possible.