Forum Discussion

KHMJ's avatar
KHMJ
Frequent Visitor
4 years ago
Solved

Conditional formatting in Matrix

Hi! I'm trying to do some conditional formatting on my matrix visual. There are a few conditions that I need to have:

1) If value > 100%, colour red

2) Based on the above, all months before the latest month that is red needs to be red too

3) Everything else that is in the first 2 months to be yellow

 

By creating a measure, I could accomplish 1) and 3), but I need some tips on how to accomplish 2).

Below is a sample data, of which i need the white cells outlined in red to be shaded in red.

Thanks!

 

 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Thank you for the reply KHMJ 
    Here is the correct measure

    Conditional Formatting =
VAR CurrentMonth =
    SELECTEDVALUE ( Table[Month Index] )
VAR T1 =
    CALCULATETABLE (
        Table,
        REMOVEFILTERS (
            Table[Year - Month],
            Table[Year Month Index],
            Table[Month Index]
        )
    )
VAR T2 =
    SUMMARIZE ( T1, [Month Index], "%Load", [% Load] )
VAR T3 =
    FILTER ( T2, [%Load] > 1 )
VAR LastMonthAbove100 =
    MAXX ( T3, [Month Index] )
VAR Result =
    IF (
        CurrentMonth <= LastMonthAbove100,
        "#ff7276",
        IF ( CurrentMonth IN { 2, 3 }, "#ffa500", "#f8f8ff" )
    )
RETURN
    Result

    In T1 variable, please make sure to remove the filter from all the columns related to year month.
    Have a great day!

8 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi KHMJ 

    it depends on the current measure. We meed to modify the existing one not to add a new measure. Can share the code that you are using right now?

    • KHMJ's avatar
      KHMJ
      Frequent Visitor
      4 years ago

      Hi tamerj1 ,

      I have a month index that is tied to the months now (i.e. 2022-03 has index 1, 2022-04 has index 2, 2022-05 has index 3 etc).

      My current measure for the formatting is:

      Conditional Formatting = IF([% Load]>1, "#ff7276", (IF((sum(Table[Month Index])=1 || sum(Table[Month Index])=2), "#ffa500", "#f8f8ff")))
      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi KHMJ 

        You may try the following

         

        Conditional Formatting =
VAR CurrentMonth =
    SELECTEDVALUE ( Table[Month Index] )
VAR LastMonthAbove100 =
    CALCULATE (
        MAX ( Table[Month Index] ),
        [% Load] > 1,
        REMOVEFILTERS ( Table[Month Index] )
    )
RETURN
    IF (
        CurrentMonth <= LastMonthAbove100,
        "#ff7276",
        IF ( CurrentMonth = 1 || CurrentMonth = 2, "#ffa500", "#f8f8ff" )
    )

        Or 

        Conditional Formatting =
VAR CurrentMonth =
    SELECTEDVALUE ( Table[Month Index] )
VAR LastMonthAbove100 =
    CALCULATE (
        MAX ( Table[Month Index] ),
        [% Load] > 1,
        REMOVEFILTERS ( Table[Month Index] )
    )
RETURN
    IF (
        CurrentMonth <= LastMonthAbove100,
        "#ff7276",
        IF ( CurrentMonth IN { 1, 2 }, "#ffa500", "#f8f8ff" )
    )

         

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Ho KHMJ 

    can you please share some insensitive sample data to work with? What is the code for % Load?

    • KHMJ's avatar
      KHMJ
      Frequent Visitor
      4 years ago

      Hi tamerj1 ,

       

      These are 2 columns that I've created:

       

      Cumulative Load = CALCULATE(SUM(Table[Qty]), FILTER(ALLEXCEPT(Table,Table[Product]),Table[Month Index]<=EARLIER(Table[Month Index])))

       

      Cumulative Capacity = CALCULATE(SUM(Table2[Qty]), FILTER(ALLEXCEPT(Table2,Table2[Product]),Table2[Month Index]<=EARLIER(Table2[Month Index])))

       

      Afterwhich, the measure was created from the 2 columns:

      % Load = SUM(Table[Cumulative Load])/SUM(Table2[Cumulative Capacity])
       
      Thanks!
      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Thank you for the reply KHMJ 
        Here is the correct measure

        Conditional Formatting =
VAR CurrentMonth =
    SELECTEDVALUE ( Table[Month Index] )
VAR T1 =
    CALCULATETABLE (
        Table,
        REMOVEFILTERS (
            Table[Year - Month],
            Table[Year Month Index],
            Table[Month Index]
        )
    )
VAR T2 =
    SUMMARIZE ( T1, [Month Index], "%Load", [% Load] )
VAR T3 =
    FILTER ( T2, [%Load] > 1 )
VAR LastMonthAbove100 =
    MAXX ( T3, [Month Index] )
VAR Result =
    IF (
        CurrentMonth <= LastMonthAbove100,
        "#ff7276",
        IF ( CurrentMonth IN { 2, 3 }, "#ffa500", "#f8f8ff" )
    )
RETURN
    Result

        In T1 variable, please make sure to remove the filter from all the columns related to year month.
        Have a great day!