How to calculate the variance to average in one column of a table

[Eric5605]

Hi, this table shows the average of survey scores (1 to 7) for all respondents grouped by agency.  I am trying to apply conditional formatting that would highlight green for any question response that is greater than 0.5 above or below the overall average.  For example question 1 has an overall average of 5.20.  Any agency averaging 4.7 or less would highlight red and any agency 5.7 or greater would highlight green.  What would be the correct DAX formula to calculate the difference of the agency's average for a particular question vs the average for all agencies for a particular question?  Thanks in advance!

[v-cgao-msft · Accepted solution]

Hi @Eric5605 ,

I assume that [Q1-Process], [Q2-Insider Threat], [Q3-Data Systems], and [Q4-Reporting] are all measures.
For [Q1-Process], you can create a new measure like this.

Measure = 
VAR __OverallAverage =
    AVERAGEX ( ALL ( 'Table'[Agency] ), [Q1-Process] )
VAR __Average = [Q1-Process]
VAR __Result =
    SWITCH (
        TRUE (),
        __Average - __OverallAverage > 0.5, "Green",
        __Average - __OverallAverage < -0.5, "Red"
    )
RETURN
    __Result

Apply it to the [Q1-Process]'s conditional formatting and the output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Eric5605]

Worked great!  Thank you!!!!!

[Ritaf1983]

Hi @Eric5605 
Please share the table (not a screenshot) to work with.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[Eric5605]

Agency	Respondents	Q1-Process	Q2-Insider Threat	Q3-Data Systems	Q4-Reporting
Department of Agriculture	915	4.90	5.22	5.15	6.13
Department of Commerce	331	5.18	5.40	5.30	6.40
Department of Defense	4180	5.32	5.45	5.33	6.41
Department of Education	104	5.42	5.59	5.49	6.25
Department of Energy	290	5.33	5.45	5.28	6.39
Department of Health and Human Services	598	5.11	5.40	5.40	6.27
Department of Homeland Security	1399	5.11	5.37	5.28	6.31
Department of Housing and Urban Development	75	5.16	5.41	5.48	6.20
Department of Justice	518	5.37	5.55	5.46	6.34
Department of Labor	141	5.24	5.38	5.34	6.14
Department of State	690	4.69	4.87	4.67	6.36
Department of the Interior	537	5.14	5.30	5.29	6.18
Department of the Treasury	688	5.31	5.48	5.46	6.27
Department of Transportation	726	5.27	5.53	5.47	6.31
Department of Veterans Affairs	1453	4.99	5.26	5.33	6.10
Environmental Protection Agency	358	4.96	5.38	5.14	6.27
General Services Administration	294	5.59	5.79	5.76	6.33
National Aeronautics and Space Administration	222	5.74	5.91	5.70	6.46
National Science Foundation	58	5.16	5.40	5.34	6.43
Nuclear Regulatory Commission	55	5.58	5.67	5.51	6.49
Office of Personnel Management	51	5.39	5.55	5.45	6.61
Small Business Administration	59	5.85	5.90	5.81	6.53
Social Security Administration	744	5.37	5.50	5.54	6.28
Total respondants and average for questions	14486	5.27	5.47	5.39	6.32