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 lat...
  • 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!

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!

  • KHMJ's avatar
    KHMJ
    Frequent Visitor
    4 years ago

    This works, thank you!