Conditional Formatting - FieldColor measure based on mutiple criteria

[Anonymous]

Hi!

With release of August update, we can color by Field Value and that includes Measure. I have a requirement where I need to compare Multiple values and color my cells accordingly.
Eg: When showing Sales in a Matrix by Region (Rows) and Products (Column),
I want to use different color codes for combination of Region and Products and Sum of Sales . I am just not getting it 😞


Here is what I tried (its obviously incorrect, how can I refer the incoming filters for current row?):

ColorCell = Switch(true(),
[Sales] <=500&& Table[Region]="EUROPE" && Table[Product]="Product 1"  ,"#b200ff",
[Sales]<=500 && Table[Region]="ASIA" && Table[Product]="Product 2" ,"#0000b2",
[Sales]<=500 && Table[Region]="AMERICAS" && Table[Product]="Product 2" ,"#b200cc")

[v-piga-msft · Accepted solution]

Hi @Anonymous,

I have made a test with the data sample similar with yours and create the measure similar with yours.

ColorCell =
SWITCH (
    TRUE (),
    SUM ( Sheet100[Value] ) <= 500
        && MAX ( 'Sheet100'[Type] ) = "EU"
        && MAX ( 'Sheet100'[Attribute] ) = "P1", "#b200ff",
    SUM ( Sheet100[Value] ) <= 500
        && MAX ( 'Sheet100'[Type] ) = "AS"
        && MAX ( 'Sheet100'[Attribute] ) = "P2", "#0000b2",
    SUM ( Sheet100[Value] ) <= 500
        && MAX ( 'Sheet100'[Type] ) = "AM"
        && MAX ( 'Sheet100'[Attribute] ) = "P2", "#b200cc"
)

Here is the ouput which I think that is right.

Best Regards,

Cherry

[Anonymous]

Thank you Cherry!!

I swear I did that same (but with MIN() ) 😄
When I went back to my old version I realized I was using a wrong region column

Thank you so much, you wont beleive how many Google search I did to find a solution. Posting here was the last restore and I should have done it first 🙂

[v-piga-msft]

Hi @Anonymous,

You're welcome!

Best  Regards,

Cherry