Forum Discussion

jobrock's avatar
jobrock
Frequent Visitor
7 years ago
Solved

How can we compare dynamic header column

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.
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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's avatar
v-juanli-msft
Community Support
7 years ago

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.