Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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
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
Worked great! Thank you!!!!!
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-...
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 |
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
61 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
103 | |
77 | |
71 |