Matrix Conditional Formatting based on Field Measure not working

[BeaudryL]

Thank you in advance for your consideration!

All details in the image - can't figure out why conditional formatting isn't working.

files if desired:

https://www.dropbox.com/scl/fi/8ulppt8k982oupje9zmdl/StdDev-Test-Cleaned.pbix?rlkey=kpt2kmhezd4y4f0n...

https://www.dropbox.com/scl/fi/uyd5cx6qd59hccldspc27/StdDev-Test-Data-Cleaned.xlsx?rlkey=turb6y56q83...

[danextian]

Hi @BeaudryL 

You didn't apply a filter modifier to your overall mean, so it simply returns the average of Response.Value. This results in the average for each combination of Clinician and ID.Q rows, which is essentially the same as your implicit measure of the average Response.Value.

The sample measure below will return the same value for all rows of MENTORS table.

Overall_Mean = 
CALCULATE ( AVERAGE ( MENTORS[Response.Value] ), ALL ( MENTORS ) )

[BeaudryL]

Almost there. Looks like it's calculating the 1_SD_Below_Mean measure line by line also? It is should be highlighting red numbers below 3.48. I would need to change that measure also? Thank you so much for your time!

[BeaudryL]

I'm not either - but the measures appear to be calculating properly and display in the cards correctly. Along your line of thought, I suppose is that if it's re-calculating line by line and applying the formatting line by line it's not working because the calculation hasn't completed until the end? 

[gmsamborn]

Hi @BeaudryL 

I noticed that your [Standard_Deviation] was returning zero for every row.

Because I'm not very experienced with STDEVX.P(), I decided to check CHAT-GPT and found the following:

STDEVX.P expects a table with multiple values per group.

You're summarizing MENTORS[Clinician] in _MeansByClincian, but @Mean is only storing a single aggregated value per clinician.

This means _MeansByClincian has only one row per clinician, making STDEVX.P return zero (since standard deviation requires variation).

Fix: Calculate standard deviation at the response level, not at the aggregated mean level. Instead of summarizing means per clinician first, compute the standard deviation directly from MENTORS[Response.Value].

__SD CHAT-GPT = 
    CALCULATE(
        STDEVX.P( 
            MENTORS, 
            MENTORS[Response.Value]
        )
    )

This, at least, doesn't return zero for each line.  You'll have to do some checking.

Let me know if you have any questions.

(I'm not sure why it is wrapped in a CALCULATE, but I just copied it.)