Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
4 years ago
Solved

Conditional formatting question

Dear experts,

 

this is the visual I have created in power BI

 and P7W4 is a measure 

P7W4 = CALCULATE( DIVIDE(SUM('WTD FY22'[CH]),SUM('WTD FY22'[EAH]),"n/a"),'WTD FY22'[Period Week]="P7WK4")
I need to conditionally put some background color based on the below table rule
 if with the combination of SSL, Rank, Lower and Upper limit
P7W4 <= Lower Limit, then Red background, if P7W4="n/a", then grey color background and if P7W4> Lower Limit, Green background
But I am not sure, how to achieve this on the Power BI desktop
SSLRankTarget UtilizationLower LimitUpper Limit
ITTSSenior75%65%85%
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi vjnvinod 

    Create a measure to return the color value .

    Measure = Switch( True() ,
[P7W4] =BLANK(),"Grey",
[P7W4] <= SELECTEDVALUE('Table'[Lower Limit]) , "red" ,
[P7W4] > SELECTEDVALUE('Table'[Lower Limit]) , "Green" )

    Then set conditional formatting for [P7W4] in table visual . Choose Field value for Format style , Measure for What field should we base this on .

     

    The final result is as shown :

    I have attached my pbix file , you can refer to it .

     

    Best Regard

    Community Support Team _ Ailsa Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    vjnvinod , That "n/a", might convert it to text

     

    But you can create a measure like

    Switch( True() ,

    [P7W4] < [Lower] , "red" ,

    [P7W4] > [Upper] , "Green" ,

    "Yellow"

    )

     

    and you can use that in conditional formatting using field value option

     

  • mh2587's avatar
    mh2587
    Super User
    4 years ago

    color measure =

    Switch( True() ,

    [P7W4] < [Lower] , "red" ,

    [P7W4] > [Upper] , "Green" ,

    "Grey"

    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi vjnvinod 

    Create a measure to return the color value .

    Measure = Switch( True() ,
[P7W4] =BLANK(),"Grey",
[P7W4] <= SELECTEDVALUE('Table'[Lower Limit]) , "red" ,
[P7W4] > SELECTEDVALUE('Table'[Lower Limit]) , "Green" )

    Then set conditional formatting for [P7W4] in table visual . Choose Field value for Format style , Measure for What field should we base this on .

     

    The final result is as shown :

    I have attached my pbix file , you can refer to it .

     

    Best Regard

    Community Support Team _ Ailsa Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.