Forum Discussion

uvil's avatar
uvil
Resolver I
7 years ago
Solved

Conditional formatting with filters

Hey all,   I have a Matrix table formed by different zone Groups, that have different % objectives, and when I apply a Conditional Formatting to the table I can't give them different values dependi...
  • Vvelarde's avatar
    Vvelarde
    7 years ago

    uvil

     

    Hi, try with this:

     

    A measure to apply the color :

     

    Color =
VAR _Sales =
    SUM ( Sales[Sales] )
VAR _Group =
    SELECTEDVALUE ( Sales[Group] )
RETURN
    SWITCH (
        _Group,
        "Group 1", SWITCH (
            TRUE (),
            _Sales <= 0,18, "#ff0000",
            _Sales < 0,21, "#ffa500",
            "#00ff00"
        ),
        "Group 2", SWITCH (
            TRUE (),
            _Sales <= 0,14, "#ff0000",
            _Sales < 0,18, "#ffa500",
            "#00ff00"
        ),
        "Group 3", SWITCH (
            TRUE (),
            _Sales <= 0,24, "#ff0000",
            _Sales < 0,28, "#ffa500",
            "#00ff00"
        )
    )

    Note: You can improve this if work with a table and the range color by each group.

     

     

    Finally , in conditional format Select Format by Field Value and choose the measure (In this case Color)

     

    Ready

     

    Victor

     

     

Vvelarde's avatar
Vvelarde
Community Champion
7 years ago

uvil

 

Hi, you can use a measure as Conditional formatting. In the measure you enter the colors that you want.

 

Regards

 

Victor

uvil's avatar
uvil
Resolver I
7 years ago

Hi, 

 

How can I use the measure as a Conditional formatting, 

 

Like Zone 1 = MinObj: 30%

                       Obj: %35

       Zone2 = MinObj: 38%

                      Obj: 45%

 

Thanks!

 

 

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    7 years ago

    uvil

     

    Hi, try with this:

     

    A measure to apply the color :

     

    Color =
VAR _Sales =
    SUM ( Sales[Sales] )
VAR _Group =
    SELECTEDVALUE ( Sales[Group] )
RETURN
    SWITCH (
        _Group,
        "Group 1", SWITCH (
            TRUE (),
            _Sales <= 0,18, "#ff0000",
            _Sales < 0,21, "#ffa500",
            "#00ff00"
        ),
        "Group 2", SWITCH (
            TRUE (),
            _Sales <= 0,14, "#ff0000",
            _Sales < 0,18, "#ffa500",
            "#00ff00"
        ),
        "Group 3", SWITCH (
            TRUE (),
            _Sales <= 0,24, "#ff0000",
            _Sales < 0,28, "#ffa500",
            "#00ff00"
        )
    )

    Note: You can improve this if work with a table and the range color by each group.

     

     

    Finally , in conditional format Select Format by Field Value and choose the measure (In this case Color)

     

    Ready

     

    Victor

     

     

    • uvil's avatar
      uvil
      Resolver I
      7 years ago

      Hey!

       

      Seems that it can work, but when I fill the measure with the values, it returns me a warning, 

       

      "Argument "n" in SWITCH function is required."  If I keep filling the function, the "n" Argument increments, and I can't solve the issue, may be im getting lost at some point.. Thanks a lot guys!

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        7 years ago

        uvil

         

        Hi, Switch Function is more useful when had 2 or more conditions to evaluate.

         

        I think after your last "; " enter the 2nd condition of GroupZon

         

        Regards

         

        Victor

         

        ------------------------------

         

        Hola Veo que también hablas español. La funcion Switch idealmente evalua más de 1 escenario. En tu caso veo que solo estás evaluando cuando GroupZon es 1. Si sólo es eso borra la última ";" . Sino a partir de ahí tienes que ingresar el siguiente escenario a evaluar.

         

         

         

         

         

    • uvil's avatar
      uvil
      Resolver I
      7 years ago

      Hey,

       

      After a few hours working with this topic i'll managed to find the solution, 

       

      Thanks a lot! It works :D

       

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    7 years ago

    Here is how I approached it.  I created a measure "Status" and based the conditional formatting on that measure. I will reverse engineering it for you here:

     

    Status = //Look for the highest delta that is > 0, assign status as appropriate
IF (
    [Uptime Delta Max] < 0,
    IF ( [Uptime Delta Tar] < 0, IF ( [Uptime Delta Th] < 0, 0, 1 ), 2 ),
    3
)

Uptime Delta Max =  //Calculate the difference between the display value and a different set value
CALCULATE (
    [Total Uptime %] - MIN ( 'Application'[Total SLA Max] )
)

Uptime Delta Tar =
CALCULATE (
    [Total Uptime %] - MIN ( 'Application'[Total SLA Target] )
)

Uptime Delta Th = 
CALCULATE( 
    [Total Uptime %] - MIN( 'Application'[Total SLA Threshold] )
)

    The measure/value that you are displaying would be the equivalent of my "Total Uptime %".  Each application has a different SLA Max, Target and Threshold, and this information is in its own table in the data model.

     

    Hope this helps

    David