Forum Discussion

bblackwell3's avatar
bblackwell3
Helper II
2 years ago
Solved

Conditionally Format Max Row Values and compare

Hey All. I have two tables, like below
1. 


2. 

 

I am trying to conditionally color the values, if( the Items = Admin and Budgeted in Table 1) matches the (Item = Admin and baseline budget value in Table 2). Then I want to be able to filter for the Leader. I was able to create the logic, using max, however this is looking at column level, where I need a row level. When filtered, I lose the color conditions in my table visual

Below is my intended result


  

 

Thanks

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi bblackwell3 

    Adjust the measure "match or not":

    Match or not = IF(LOOKUPVALUE('Table2'[Budgeted],'Table2'[Budgeted],SELECTEDVALUE('Table1'[Budgeted])),"match","not match")



    color = 
VAR _max = CALCULATE(MAX('Table1'[Budgeted]),FILTER(ALL('Table1'),[Match or not] = "match"))

RETURN
SWITCH(TRUE(),SELECTEDVALUE('Table1'[Budgeted])=_max,"green")

     

     

    Result:

     

     

    Pbix file attached.

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi bblackwell3 

     

    First, create a measure to determine if the items and budgeted in the two tables match:

    Match or not = IF(LOOKUPVALUE('Table2'[Budgeted],'Table2'[Items],SELECTEDVALUE('Table1'[Items]),'Table2'[Budgeted],SELECTEDVALUE('Table1'[Budgeted])),"match","not match")

     

    Then create a measure to apply conditional formatting to the filtered matching values

    color = 
VAR _max = CALCULATE(MAX('Table1'[Budgeted]),FILTER(ALL('Table1'),[Match or not] = "match"))

RETURN
SWITCH(TRUE(),SELECTEDVALUE('Table1'[Budgeted])=_max,"green")

     

     

    Result:

     

     

     

    Best Regards,

    Jayleny

     

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

    • bblackwell3's avatar
      bblackwell3
      Helper II
      2 years ago

      This could be a possible solution. However, I made a mistake on my end. I actually do not have an Items column in Table 2. How would this alter the logic?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi bblackwell3 

        Adjust the measure "match or not":

        Match or not = IF(LOOKUPVALUE('Table2'[Budgeted],'Table2'[Budgeted],SELECTEDVALUE('Table1'[Budgeted])),"match","not match")



        color = 
VAR _max = CALCULATE(MAX('Table1'[Budgeted]),FILTER(ALL('Table1'),[Match or not] = "match"))

RETURN
SWITCH(TRUE(),SELECTEDVALUE('Table1'[Budgeted])=_max,"green")

         

         

        Result:

         

         

        Pbix file attached.

         

         

         

         

         

         

        Best Regards,

        Jayleny

         

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