How can we compare dynamic header column

[jobrock]

Hi

How can we compare column - my condition is

1. Mark cells red if the cost per lead is 20% higher then the day before

2. Mark cells red if Lead per day is 0 and Cost per day is 0.

[v-juanli-msft · Accepted solution]

Hi @jobrock 

Please pay attention to this formula

previous value =
CALCULATE (
    SUM ( Table1[value] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        Table1[client] = MAX ( Table1[client] )&&Table[spend]=MAX(Table[spend])&&Table[day]=MAX(Table[day])&&...
            && Table1[metric] = "cost per lead"
            && Table1[date]
                = MAX ( Table1[date] ) - 1
    )
)

add any columns in the "Row" fields of the matrix in the red part.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @jobrock 

Create measures

previous value =
CALCULATE (
    SUM ( Table1[value] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        Table1[client] = MAX ( Table1[client] )
            && Table1[metric] = "cost per lead"
            && Table1[date]
                = MAX ( Table1[date] ) - 1
    )
)

pre_vs_cu =
VAR per =
    ( SUM ( Table1[value] ) - [previous value] ) / [previous value]
RETURN
    IF ( [previous value] <> BLANK (), per )

color flag1 = IF([pre_vs_cu]>0.2&&MAX(Table1[metric])="cost per lead",1,0)

color flag 2 = IF(SUM(Table1[value])=0&&MAX(Table1[metric])in {"cost per lead","lead per day"},1,0)

final flag = IF([color flag1]=1||[color flag 2]=1,1,0)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.