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 depending to the zone group,

 

I mean, the % objectives are different for each group, that's for this, that I can't apply a generic formatting value... And I don't know how to resolve the issue, and maybe, there's an option inside Power BI, that can help me to resolve this problem,

 

I want something as showed in the link below, and maybe it's difficult to reproduce in Power BI,

 

Thanks a lot guys!

 

https://gyazo.com/6a53142423e5d7f99fb6b71b12090f51

  • 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

     

     

9 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    7 years ago

    I've had to do something similar, where I calculated the variance from the objective in a hidden column/measure, and did conditional formatting based on the hidden column.

     

    This is, of course, assuming that there is some uniformity among the colors as relates to the variance.

     

    If this explanation doesn't make sense I'll try to dig up the report where I did it.

     

    Hope this helps

    David

  • 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