Forum Discussion

Friso's avatar
Friso
Frequent Visitor
5 years ago
Solved

Conditional formatting row level

Hello,  

 

The conditional formatting is normally based on the collumn. When I hav made a Matrix (for example MONTHS in de Collumns and categories (ladies, gentleman and kids) on the rows with de difference in turnover (Index%), I would like the negative numebrs red and positive in green. Not only "FULL" red and green but it is depending on the most lowest or highest outcome. 

 

For example : lowest = -15% -Darkred....-10% Medium Red and -5 % light red.....

 

Etc...

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Friso ,

     

    According to my understanding, you want to set gradient color sperately for negative and positive number, right?

    You could use the following formula to create a measure and apply it to conditional formatting.

     

    Measure =
IF (
    MAX ( 'Table'[Turnover] ) <= -0.15,
    "#8B0000",
    IF (
        MAX ( 'Table'[Turnover] ) > -0.15
            && MAX ( 'Table'[Turnover] ) <= -0.1,
        "#FF0000",
        IF (
            MAX ( 'Table'[Turnover] ) > -0.1
                && MAX ( 'Table'[Turnover] ) <= 0,
            "#F08080",
            IF (
                MAX ( 'Table'[Turnover] ) > 0
                    && MAX ( 'Table'[Turnover] ) <= 0.05,
                "#ADFF2F",
                IF (
                    MAX ( 'Table'[Turnover] ) > 0.05
                        && MAX ( 'Table'[Turnover] ) <= 0.1,
                    "#32CD32",
                    "#006400"
                )
            )
        )
    )
)

     

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

     

    Best Regards,
    Eyelyn Qin

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Friso ,

     

    According to my understanding, you want to set gradient color sperately for negative and positive number, right?

    You could use the following formula to create a measure and apply it to conditional formatting.

     

    Measure =
IF (
    MAX ( 'Table'[Turnover] ) <= -0.15,
    "#8B0000",
    IF (
        MAX ( 'Table'[Turnover] ) > -0.15
            && MAX ( 'Table'[Turnover] ) <= -0.1,
        "#FF0000",
        IF (
            MAX ( 'Table'[Turnover] ) > -0.1
                && MAX ( 'Table'[Turnover] ) <= 0,
            "#F08080",
            IF (
                MAX ( 'Table'[Turnover] ) > 0
                    && MAX ( 'Table'[Turnover] ) <= 0.05,
                "#ADFF2F",
                IF (
                    MAX ( 'Table'[Turnover] ) > 0.05
                        && MAX ( 'Table'[Turnover] ) <= 0.1,
                    "#32CD32",
                    "#006400"
                )
            )
        )
    )
)

     

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

     

    Best Regards,
    Eyelyn Qin

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Friso , Not very clear. You need to conditional formatting for all columns/values. There is nothing like row conditional formatting.

     

    The second option, but is difficult in terms of the color you need. Create a color measure as per need based on the given example and use that in conditional formatting with the field Value option

     

    example

    color Measure = 
Switch(True() ,
max(Table[ID]) in {10,20,30} ,"Red",
max(Table[ID]) in {5,15,25} ,"Blue",
"White"
)
maxx(filter(Appointments, Appointments[Status]="Completed" && Appointments[Customer ID] = Customer[Customer ID]), lastnonblankvalue(Appointments[Appt Date Time], Appointments[Provider]))


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

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

     

    https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
    https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values