Forum Discussion

jonbox's avatar
jonbox
Helper II
4 years ago
Solved

Colour format based on rule in separate column

Hi, 

 

I have a fairly simple question but can't get the logic down on powerBI...

Currently using the traditional conditional formatting i'm only setting a colour based on one metric e.g. if over 0.9, green...

What I would like to do is take the "goals" column and look at each row e.g. on one row the goal is 1, next goal is 0.9 etc and based the way the next column is formatted based on those goals;

 

for example using the below image; the goal for the first and second row is 1, so the july coloumn should only show green if equal to that, whereas the fourth row goals is 100%, so should only show green if 100%. Finally the fifth row goal is 0.9, so july should only show green if equal or above 0.9.

Issue with the traditional conditional format feature is that you can only base a column off of one rule:

 

 

Any help with this would be massively appreciated. thanks!

 

 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, jonbox 

     

    Try this:

    Measure = 
IF(
    NOT(ISBLANK(MAX('Table'[Jul]))),
    SWITCH(
        TRUE(),
        MAX('Table'[Goals])<=MAX('Table'[Jul]),"Green",
        "red"))

    Result:

    Please refer to the attachment below for details. Hope this helps.

     

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    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

    jonbox , You can create a color measure and use that in condition formatting using field value option

     

    example


    Color = if(FIRSTNONBLANK('Table'[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170
    ,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))

     

    Colour =
    SWITCH(TRUE(),
    max('Table'[Month Year]) = "Jul-2020" && [Measure] <.9, "red",

    max('Table'[Month Year]) = "Jul-2020" && [Measure] >=.9, "Green",
    Max('Table'[Month Year]) < "Aug-2020", "orange",
    //keep on adding
    "green")

     

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

    • jonbox's avatar
      jonbox
      Helper II
      4 years ago

      Hi Amit, 

       

      Thanks for the reply. How would this fit in my use case? looking at you example i'm struggling to put it into place.

       

      As i mentioned, i'd really like for the measure to look at the goals column for each row, see for row 2 the goal is one, check the july column for each row, see for row 2 if it is above or below the goal for that row and colour the july row 2 either green or red.

       

       

       

       

      I'm quite new to using mesaures in powerbi so hence why i need the help!

       

      Thanks again.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, jonbox 

     

    Try this:

    Measure = 
IF(
    NOT(ISBLANK(MAX('Table'[Jul]))),
    SWITCH(
        TRUE(),
        MAX('Table'[Goals])<=MAX('Table'[Jul]),"Green",
        "red"))

    Result:

    Please refer to the attachment below for details. Hope this helps.

     

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


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