Forum Discussion
conditional formatting for large table based on measures
Hi, I am working in excel power query and wondered if there was an easy way to conditionally format my pivot table columns based on measure values. The columns are calculated measures (for difference and confidence interval for each question) based on e.g. Regions - like example below. There are many regions and therefore many repeating measures and I wondered if it was possible to set conditional formatting up so it applies to all regions in my large table in one go. So fornatted green or red where difference is greater than or less that confidence interval (+/-).
| Region a | Region b | |||
| Difference | Confidence interval +/- | Difference | Confidence interval +/- | |
| q1 | 7 | 6 | 4 | 2 |
| q2 | -10 | 9 | -3 | 1 |
Many thanks,
A
4 Replies
Hello adavid999
this is no Power Query question and not even Power BI question 🙂
But I'm trying to answer nevertheless
You can accomplish this with the normal conditional formatting of Excel. You can use this formula as guideline
=OR(AND(B$2="Difference";B3>C3);AND(B$2="Confidence interval +/-";A3>B3))
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi Jimmy801 thanks for responding. I am using power query/calculated measures to generate values in my pivot table and wanted to know if it were possible to set up conditional formatting based on these, rather than visible cell location specific. So for example whether it was possible to conditionally format a value shown in my pivot table based on the value of a related calculated measure, not necessarily shown in the pivot table.
ps, so formatting visible % column, based on measurs Diff and CI - without showing these values in pivot table
