conditional formatting for large table based on measures

[adavid999]

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

[Jimmy801]

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

Jimmy

[adavid999]

Hi @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.

[Jimmy801]

Hello @adavid999 

formatting is always done in the Excel-sheet and not in the data model.

So you need to use conditional formatting as shown in my example. Columns can also be hidden

Jimmy

[adavid999]

ps, so formatting visible % column, based on measurs Diff and CI - without showing these values in pivot table