need help with a conditional formatting switch measure

[TCarrasquillo]

Good Morning,

I'm hoping somebody can help me out before I drive myself crazy. I have been working on Conditional formatting of some KPI comparing individual managers to the company wide "mean". I have a basic switch measure like this :

cwlbr/hrcolor = SWITCH(TRUE(), [*Labor/Hr]<[CWLbr/hr],"green",[*Labor/Hr]>[CWLbr/hr],"red")

then I was asked to narrow the formatting down to only the top/bottom 5 based on Revenue which took a bitbut I figured it out :

T5/B5 Lbr/Hr Color = SWITCH(TRUE(),[RevRank]<='TopN'[TopN Value] ,SWITCH(TRUE(), [*Labor/Hr]<=[CWLbr/hr],"green",[*Labor/Hr]>=[CWLbr/hr],"red"),

[RevRank]>=29-BottomN[BottomN Value] ,SWITCH(TRUE(), [*Labor/Hr]<=[CWLbr/hr],"green",[*Labor/Hr]>=[CWLbr/hr],"red"))

Now, I've been asked if it's possible instead to format the values that are 1 or 2 standard deviations from the mean (+ and -) so I have all the necessary measures but can't seem to get them together to get the formatting working.

Here is the measure I've been working with, I've played with a lot of different variations, changing the  

Deviation Color = SWITCH(TRUE(),

[*Labor/Hr]>[+1StDev.Lbr/Hr], "yellow",

[*Labor/Hr]>[+2StDev.Lbr/Hr], "red",

[*Labor/Hr]<[-2StDev.Lbr/Hr], "blue",

[*Labor/Hr]<[-1StDev.Lbr/Hr], "green" )

I've also tried this but it doesn't work.

Test DevColor = SWITCH(TRUE(),[*Labor/Hr]>=[CWLbr/hr] ,SWITCH(TRUE(), [*Labor/Hr]>[+2StDev.Lbr/Hr],"red",[*Labor/Hr]>[+1StDev.Lbr/Hr],"yellow"),

[*Labor/Hr]<=[CWLbr/hr] ,SWITCH(TRUE(), [*Labor/Hr]<[-2StDev.Lbr/Hr],"blue",[*Labor/Hr]<[-1StDev.Lbr/Hr],"green"))

Any help on this would be appreciated, I feel like it's just right there and I'm not seeing it.

[v-juanli-msft]

Hi @TCarrasquillo 

First calculate mean and standard deviations based on the same category.

(it detemines on your actual scenario, here it is just an example)

Then calcualte the column you need.

mean = CALCULATE(AVERAGE(Sheet4[c2]),FILTER(Sheet4,Sheet4[cate]=EARLIER(Sheet4[cate])))

STDEV = CALCULATE(STDEV.S([c2]),FILTER(Sheet4,Sheet4[cate]=EARLIER(Sheet4[cate])))

Column =
IF (
    [c1] > [mean] + 2 * [STDEV],
    "red",
    IF (
        [c1] > [mean] + 1 * [STDEV],
        "yellow",
        IF (
            [c1] < [mean] - 2 * [STDEV],
            "blue",
            IF ( [c1] < [mean] - 1 * [STDEV], "green", "other" )
        )
    )
)

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.

[TCarrasquillo]

Thanks for replying but, that option doesn't seem to work for me.

When I try to do the conditional formatting, it will not let me pick the column if  I choose "field value" and it doesn't like the "rules" option either as the column is text. It tries to summarize it by count or first/last.

[v-juanli-msft · Accepted solution]

Hi @TCarrasquillo 

Create a measure

Measure =
VAR mean1 =
    CALCULATE (
        AVERAGE ( Sheet4[c2] ),
        FILTER ( ALL ( Sheet4 ), Sheet4[cate] = MAX ( Sheet4[cate] ) )
    )
VAR stdev1 =
    CALCULATE (
        STDEV.S ( [c2] ),
        FILTER ( ALL ( Sheet4 ), Sheet4[cate] = MAX ( Sheet4[cate] ) )
    )
RETURN
    IF (
        MAX ( [c1] ) > mean1 + 2 * stdev1,
        1,
        IF (
            MAX ( [c1] ) > mean1 + 1 * stdev1,
            2,
            IF (
                MAX ( [c1] ) < mean1 - 2 * stdev1,
                3,
                IF ( MAX ( [c1] ) < mean1 - 1 * stdev1, 4, 5 )
            )
        )
    )

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.