Conditional formatting

[QAA91]

Planned	Actual
100%	100%
50%	50%
60%	80%
40%	20%
blank	blank

Hi, how to apply background colour based on conditions above.

1. Both Planned and Actual equal to 100% - format the cell to BLUE

2. Both Planned and Actual are equal to each other but less than 100% - format the cell to GREEN

3. Actual greater than Planned - format the cell to GREEN

4. Actual less than Planned - format the cell to RED

5. Both Planned and Actual blank - format the cell to GRAY

[AilleryO · Accepted solution]

Hi,

You can create a measure like this :

Colors of Tasks = SWITCH( TRUE(),

SELECTEDVALUE( TaskList[Actual] ) = SELECTEDVALUE( TaskList[Planned] ) && SELECTEDVALUE( TaskList[Actual] )=1 , "#0000CD", //blue

SELECTEDVALUE( TaskList[Actual] ) = SELECTEDVALUE( TaskList[Planned] ) && SELECTEDVALUE( TaskList[Actual] )<1 , "#98FB98", //green

SELECTEDVALUE( TaskList[Actual] ) > SELECTEDVALUE( TaskList[Planned] ) , "#7FFF00", //green other

SELECTEDVALUE( TaskList[Actual] ) < SELECTEDVALUE( TaskList[Planned] ) , "#FF0000", //red

ISBLANK( SELECTEDVALUE( TaskList[Actual] ) ) && ISBLANK( SELECTEDVALUE( TaskList[Planned] ) ) , "#D3D3D3", //grey

"#FFFFFF" //else white

)

and then use it in Conditionnal formatting using (values of fields) :

Hope it make sense for you otherwise do not hesitate to ask for more details.

Have a nice day

[amitchandak · Accepted solution]

@QAA91 , You can create a measure like

Assuming they are % column

color

Switch( True() ,

[Planned] =1 && [Actual] =1, "Blue" ,

([Planned] =[Actutal] && [Actual] <> 1) || [Planned] < [Actutal] , "Green" ,

[Planned] > [Actutal] , "Red",

"Grey"

)

Use this in conditional formating using the field value option.

A blank value will not take any color

PowerBI Abstract Thesis: How to do conditional formatting by measure and apply it on pie? : https://youtu.be/RqBb5eBf_I4

[ao292]

Still on my journey in Power BI but check out my answer below:

Indicator = SWITCH(

TRUE(),

Data[Planned] = 1 && Data[Actual] = 1, 0,

Data[Planned] = Data[Actual] && Data[Planned] < 1 && Data[Actual] < 1, 1,

Data[Actual] > Data[Planned], 2,

Data[Actual] < Data[Planned], 3,

ISBLANK(Data[Actual]) && ISBLANK(Data[Planned]), 4

)

After set up the conditional formatting as below:

[AilleryO · Accepted solution]

Hi, Sorry one mistake in my formula, you should do the test for blank at first otherwise when both are blanks you'll get green because both have same value ;-=

So the formula should be :

Colors of Tasks = SWITCH( TRUE(),

ISBLANK( SELECTEDVALUE( TaskList[Actual] ) ) && ISBLANK( SELECTEDVALUE( TaskList[Planned] ) ) , "#D3D3D3", //grey

SELECTEDVALUE( TaskList[Actual] ) = SELECTEDVALUE( TaskList[Planned] ) && SELECTEDVALUE( TaskList[Actual] )=1 , "#0000CD", //blue

SELECTEDVALUE( TaskList[Actual] ) = SELECTEDVALUE( TaskList[Planned] ) && SELECTEDVALUE( TaskList[Actual] )<1 , "#98FB98", //green

SELECTEDVALUE( TaskList[Actual] ) > SELECTEDVALUE( TaskList[Planned] ) , "#7FFF00", //green other

SELECTEDVALUE( TaskList[Actual] ) < SELECTEDVALUE( TaskList[Planned] ) , "#FF0000", //red

"#FFFFFF" //else white

)

Do not forget to check as solved if this works as expected

[amitchandak · Accepted solution]

@QAA91 , You can create a measure like

Assuming they are % column

color

Switch( True() ,

[Planned] =1 && [Actual] =1, "Blue" ,

([Planned] =[Actutal] && [Actual] <> 1) || [Planned] < [Actutal] , "Green" ,

[Planned] > [Actutal] , "Red",

"Grey"

)

Use this in conditional formating using the field value option.

A blank value will not take any color

PowerBI Abstract Thesis: How to do conditional formatting by measure and apply it on pie? : https://youtu.be/RqBb5eBf_I4

[QAA91]

Thank you very much

[AilleryO · Accepted solution]

Hi,

You can create a measure like this :

Colors of Tasks = SWITCH( TRUE(),

SELECTEDVALUE( TaskList[Actual] ) = SELECTEDVALUE( TaskList[Planned] ) && SELECTEDVALUE( TaskList[Actual] )=1 , "#0000CD", //blue

SELECTEDVALUE( TaskList[Actual] ) = SELECTEDVALUE( TaskList[Planned] ) && SELECTEDVALUE( TaskList[Actual] )<1 , "#98FB98", //green

SELECTEDVALUE( TaskList[Actual] ) > SELECTEDVALUE( TaskList[Planned] ) , "#7FFF00", //green other

SELECTEDVALUE( TaskList[Actual] ) < SELECTEDVALUE( TaskList[Planned] ) , "#FF0000", //red

ISBLANK( SELECTEDVALUE( TaskList[Actual] ) ) && ISBLANK( SELECTEDVALUE( TaskList[Planned] ) ) , "#D3D3D3", //grey

"#FFFFFF" //else white

)

and then use it in Conditionnal formatting using (values of fields) :

Hope it make sense for you otherwise do not hesitate to ask for more details.

Have a nice day

[QAA91]

Thank you very much