Forum Discussion

QAA91's avatar
QAA91
Regular Visitor
4 years ago
Solved

Conditional formatting

PlannedActual
100%100%
50%50%
60%80%
40%20%
blankblank

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's avatar
    AilleryO
    4 years ago

    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's avatar
    amitchandak
    4 years ago

    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

  • AilleryO's avatar
    AilleryO
    4 years ago

    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

6 Replies

  • AilleryO's avatar
    AilleryO
    Memorable Member
    4 years ago

    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's avatar
    amitchandak
    Super User
    4 years ago

    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

  • AilleryO's avatar
    AilleryO
    Memorable Member
    4 years ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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: