Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Conditional formatting for one hierarchy level

I have a matrix where I want conditional formatting to apply only to one level of the hierarchy, the "name" level.

 

The rows "Anthony Heaven" and "Cassie Mesko" should have a background color, but nothing above or below this hierarchy level. This level of the hierarchy is called "Resource".

 

The conditional formatting should run on a different measure called "Utilization Rate". If Utilization Rate is greater than 0.90 (90%), the cell should become red. If it is 70%-90%, yellow. If it is 70% or less, green.

 

Looking through other posts, it looks like the best course of action is to create a measure and then use that as the basis for conditional formatting, but I cannot get it to work.

 

I created this measure:

Utilization Color =
VAR Utilization = 'Deployment'[Utilization Rate]
RETURN
IF(NOT(ISINSCOPE('Deployment'[Resource])),(if(Utilization>.90,"Red","Green")))
 
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Anonymous ,

    I created some data:

    Here are the steps you can follow：

    1. Create measure.

     

    Color =
SWITCH(
TRUE(),
    ISINSCOPE('Table'[Group3]) && [Utilization Rate] >0.9 ,"red",
    AND(ISINSCOPE('Table'[Group3]) ,[Utilization Rate] >0.7) && AND(ISINSCOPE('Table'[Group3]) ,[Utilization Rate] <=0.9),"yellow",
    ISINSCOPE('Table'[Group3]) && [Utilization Rate] <=0.7 ,"green")

     

    2. Conditional formatting – Background color.

    3. Enter Background color.

    Format style – Field value

    What field should we base this on – [Color]

    4. Result:

     

    Best Regards,

    Liu Yang

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello, Have you found a solution to this?

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Yes, I did. If it's helpful to you:

       

      Conditional formatting: Background Color

      Format style: Field value

      Apply to: Values and Totals

      What field should we base this on: conditionalformatting

       

      Formula for that field:

      conditonalformatting =
          VAR varLevel2 = ( 90 / 100 )
          VAR varLevel1 = ( 35 / 1000 )
          RETURN
           SWITCH (
                               TRUE(),
                 ISINSCOPE ('Projector Deployment Report Current and Planned'[Stage]), BLANK (),
                 ISINSCOPE ('Resource Assigned Practice Group'[Name])   &&
                                     ([Utilization Rate]) >= varLevel2, "#efb5b9",
                 ISINSCOPE ('Resource Assigned Practice Group'[Name])   &&
                                    ( ([Utilization Rate]) >= 0.7 && ([Utilization Rate]) < 0.9), "#f0e199",
        ISINSCOPE ('Resource Assigned Practice Group'[Name])   &&
                                    ([Utilization Rate]) < 0.7, "#d0ef9e",
       "#ffffff"
      )