Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with conditional formatting

Hello, for some reason I can't figure out how to type a formula for conditional formatting in this new version of Power BI.    I would love some help to figure that out, but to also conditional for...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    Anonymous 

    The only way I could think to do it is with 3 formatting measures becuse you have 3 different compare points.

     

    2021 format = 
VAR _Field = [2021]
VAR _Compare1 = [YTD]
VAR _Compare2 = [Latest Month]
RETURN
SWITCH(
    TRUE(),
    _Field > _Compare1 && _Field > _Compare2, "Green",
    _Field < _Compare1 && _Field > _Compare2, "Yellow",
    _Field > _Compare1 && _Field < _Compare2, "Yellow",
    _Field < _Compare1 && _Field < _Compare2, "Red"
)
    YTD format = 
VAR _Field = [YTD]
VAR _Compare1 = [2021]
VAR _Compare2 = [Latest Month]
RETURN
SWITCH(
    TRUE(),
    _Field > _Compare1 && _Field > _Compare2, "Green",
    _Field < _Compare1 && _Field > _Compare2, "Yellow",
    _Field > _Compare1 && _Field < _Compare2, "Yellow",
    _Field < _Compare1 && _Field < _Compare2, "Red"
)
    Latest Month format = 
VAR _Field = [Latest Month]
VAR _Compare1 = [2021]
VAR _Compare2 = [YTD]
RETURN
SWITCH(
    TRUE(),
    _Field > _Compare1 && _Field > _Compare2, "Green",
    _Field < _Compare1 && _Field > _Compare2, "Yellow",
    _Field > _Compare1 && _Field < _Compare2, "Yellow",
    _Field < _Compare1 && _Field < _Compare2, "Red"
)

     

    Then you apply each to the corresponding field as the background color.

     

jdbuchanan71's avatar
jdbuchanan71
Super User
4 years ago

Anonymous 

The only way I could think to do it is with 3 formatting measures becuse you have 3 different compare points.

 

2021 format = 
VAR _Field = [2021]
VAR _Compare1 = [YTD]
VAR _Compare2 = [Latest Month]
RETURN
SWITCH(
    TRUE(),
    _Field > _Compare1 && _Field > _Compare2, "Green",
    _Field < _Compare1 && _Field > _Compare2, "Yellow",
    _Field > _Compare1 && _Field < _Compare2, "Yellow",
    _Field < _Compare1 && _Field < _Compare2, "Red"
)
YTD format = 
VAR _Field = [YTD]
VAR _Compare1 = [2021]
VAR _Compare2 = [Latest Month]
RETURN
SWITCH(
    TRUE(),
    _Field > _Compare1 && _Field > _Compare2, "Green",
    _Field < _Compare1 && _Field > _Compare2, "Yellow",
    _Field > _Compare1 && _Field < _Compare2, "Yellow",
    _Field < _Compare1 && _Field < _Compare2, "Red"
)
Latest Month format = 
VAR _Field = [Latest Month]
VAR _Compare1 = [2021]
VAR _Compare2 = [YTD]
RETURN
SWITCH(
    TRUE(),
    _Field > _Compare1 && _Field > _Compare2, "Green",
    _Field < _Compare1 && _Field > _Compare2, "Yellow",
    _Field > _Compare1 && _Field < _Compare2, "Yellow",
    _Field < _Compare1 && _Field < _Compare2, "Red"
)

 

Then you apply each to the corresponding field as the background color.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

thanks for the help!