Applying specific conditional formatting across individual rows of a table

[apkmnp]

Hi Team,

I have a scenario where I need to apply customized conditional formatting based on the SKUs and a factor for each SKU, however it seems the rules defined at the excel level are very specific to each SKU and there is no pattern available for me to replicate here.

Need some guidance on how can I apply these custom format to powerbi level, I understand that "Field Value" needs to be used here but not sure how to approach ahead. Each SKU has a different conditional formatting rule, sharing some examples

Eg for Row1:

Conditional Formatting Structure for row1:

Eg for row2:

Conditional Formatting for row2:

Sharing the complete file link with the exact conditional formatting required for 5 sample cases. Looking forward to some great responses.

(File link) Conditional Formatting required 

Best Regards,

Apkmnp

[apkmnp]

Hi, yes you are right, the factor just highlights the upper limit for color assignment in any row and does not have any particular correlation with the color, the main question here is if  I do need to map the seperate logic for all the rows here into a single measure as all the rows have different logic which is not comprehensible right now.

Eg for Row1:

Conditional Formatting Structure for row1:

Eg for row2:

Conditional Formatting for row2:

[v-rzhou-msft · Accepted solution]

Hi @apkmnp ,

I suggest you to transform your data model as below, then you can create a matrix and build measure which can be used in background conditional formatting.

Measure:

Measure = 
SWITCH(
MAX('Table'[SKU]),
"ABC1",
SWITCH(TRUE(),SUM('Table'[Value])<=0,"Red",SUM('Table'[Value])<2,"Yellow",SUM('Table'[Value])<6,"Grey",SUM('Table'[Value])<= 32,"Cyan","Light Blue"),
"ABC2",
SWITCH(TRUE(),SUM('Table'[Value])<=2,"RED",SUM('Table'[Value])<4,"Yellow",SUM('Table'[Value])<8,"Grey",SUM('Table'[Value])<=13,"Cyan","Light Blue")
)

Here we select Field Value as Format Style and choose the measure.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[serpiva64]

Sorry,

from your exemple is not possible to understand, it's clear that conditional formatting depends on the factor but it is not possible to me to identify any correlation with colours.

Can you  try to explain?