Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to conditonal format based off another cell?

Hi, 

 

I am having trouble trying to work this out. I want to conditon format cell values, based off their previous cell in a matrix table below.

For example, if a value is was higher than the previos year then colour it green, if it was lower than red and the same would be coloured orange. 

 

 

ANy help would be much appreciated!

 

Thanks

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    create a measure like below, and put it into the background color formatting option.

    Background color measure: =
VAR _currentvalue = [Value measure:]
VAR _prevyearvalue =
    CALCULATE ( [Value measure:], 'Year'[Year] = MAX ( 'Year'[Year] ) - 1 )
RETURN
    IF (
        NOT ISBLANK ( _prevyearvalue ),
        SWITCH (
            TRUE (),
            _currentvalue > _prevyearvalue, "Green",
            _currentvalue = _prevyearvalue, "Orange",
            _currentvalue < _prevyearvalue, "Red"
        )
    )

     

     

    And then, select Field value option, like below.

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks, works perfectly!!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    create a measure like below, and put it into the background color formatting option.

    Background color measure: =
VAR _currentvalue = [Value measure:]
VAR _prevyearvalue =
    CALCULATE ( [Value measure:], 'Year'[Year] = MAX ( 'Year'[Year] ) - 1 )
RETURN
    IF (
        NOT ISBLANK ( _prevyearvalue ),
        SWITCH (
            TRUE (),
            _currentvalue > _prevyearvalue, "Green",
            _currentvalue = _prevyearvalue, "Orange",
            _currentvalue < _prevyearvalue, "Red"
        )
    )

     

     

    And then, select Field value option, like below.