Forum Discussion

TCarrasquillo's avatar
TCarrasquillo
Helper II
6 years ago
Solved

need help with a conditional formatting switch measure

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

    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.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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's avatar
      TCarrasquillo
      Helper II
      6 years ago

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

        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.