cancel
Showing results for
Did you mean:
Highlighted
Member

## Switch & Calculate Issue

Hi Folks,

I'm trying to set up a performance type scenario. What I want to do is this:

If they received between a 3 or 3.5 in FY16 and FY17 then they are a core performer. If they received a 4 or higher in FY16 and FY17 then they are a high performer. If they received less than a 3 in FY16 and FY17  then I want to say they are a low performer.

Some logic I have written is this:

Performance Type = Switch (
True(),
Calculate ( Count ('Rating-FY17'[FY17 Rating]),'Rating-FY17'[FY17 Rating] >= 3) &&
Calculate ( Count ('Rating-FY17'[FY17 Rating]),'Rating-FY17'[FY17 Rating] <= 3.5) &&
Calculate ( Count ('Rating-FY16'[Overall Performance Rating]),'Rating-FY16'[Overall Performance Rating] >= 3) &&
Calculate ( Count ('Rating-FY16'[Overall Performance Rating]),'Rating-FY16'[Overall Performance Rating] <= 3.5), "Core Performer",
Calculate ( Count ('Rating-FY17'[FY17 Rating]),'Rating-FY17'[FY17 Rating] < 3) &&
Calculate ( Count ('Rating-FY16'[Overall Performance Rating]),'Rating-FY16'[Overall Performance Rating] < 3), "Low Performer",
Calculate ( Count ('Rating-FY17'[FY17 Rating]),'Rating-FY17'[FY17 Rating] >= 4) &&
Calculate ( Count ('Rating-FY16'[Overall Performance Rating]),'Rating-FY16'[Overall Performance Rating] >= 4), "High Performer",
"N/A"
)

However, in looking at the data, it doesn't seem to be calculating correctly for "Low Performers" it's not picking up those have had < 3 for the last 2 years for whatever reason.

Additionally, I feel like some sort of average needs to be rolled in here, too, because, what if they received a 3 in FY16 and a 4 in FY17, the average would then be a 3.5 and would be a Core Performer rather than N/A. What can I do for this? Any help would be great please.

The healthcare company I work for is super strict with performance but we need to identify this easily. Any help would be much appreciated!

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Switch & Calculate Issue

You just have to work this until you get your logic correct.

For example, by your stated rules, Donald Duck gets an N/A because his FY17 score is NOT less than 3.

```Performance? =
VAR Rating = SWITCH(
TRUE(),
[FY16]>=4 && [FY17]>=4,"High Performer",
([FY16]>=3 && [FY16] <= 3.5) && ([FY17] >= 3 && [FY17]<=3.5),"Core Performer",
[FY16] < 3 && [FY17] < 3,"Low Performer",
"N/A"
)
RETURN Rating```

For your second one, maybe something like this:

```Performance2 =
VAR Score = [FY17]+[FY16]/2
VAR Rating = SWITCH(
TRUE(),
Score>=4,"High Performer",
Score>=3 && Score <= 3.5,"Core Performer",
Score < 3,"Low Performer",
"N/A"
)
RETURN Rating```

These formulas work for their defined rules, but you will have to edit in your own rules.

Proud to be a Datanaut!

9 REPLIES 9
Member

## Re: Switch & Calculate Issue

Anyone? @Greg_Deckler

Super User

## Re: Switch & Calculate Issue

You didn't post sample data so I created some:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTVfSUTIB41idaCWnzJycSgWn/CSgiBEYg0S9MnOT8oE8Yz1TEAkWi8osKKgE88B6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t, FY17 = _t, FY16 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"FY17", type number}, {"FY16", type number}})
in
#"Changed Type"```

I then creaed this measure, which I think is simpler than the one you have and also solves the problem of average score.

```Rating =
VAR FY17 = SUM(Performance[FY17])
VAR FY16 = SUM(Performance[FY16])
VAR Score = FY17+FY16
VAR Rating = SWITCH(
TRUE(),
Score>=6 && Score <= 7,"Core Performer",
Score < 6,"Low Performer",
Score>7,"High Performer"
)
RETURN Rating```

Not sure. I made the assumption here that a score above 7 is a high performer. Otherwise, what happens to someone who falls in between 7 and 8?

Proud to be a Datanaut!

Member

## Re: Switch & Calculate Issue

Here is an example of the data:

 EE ID Name FY17 Rating FY16 Rating Performer? 1234 Skellington, Jack 4 4 High 2345 Pooh, Winnie 4 3 Core 3456 Mouse, Mickey 5 2 Core 4567 Duck, Donald 1 3 Low

I want a calculated column (added to the last column to show an example) that says:

• If an employee receives >= 4 in FY16 and FY17 then they are a high performer
• If an employee receives between a 3 and 3.5 in FY16 and FY17 then they are a core performer
• If an employee receives less than a 3 in FY16 and FY17 then they are a low performer
• Otherwise, "N/A" - My logic that I wrote in my original post I think is messed up somewhere.

The second piece to that is this:

Looking at Winnie The Pooh, he has a 3 and a 4. Well, what happens to him? He gets an "N/A" based on first piece of logic because he doesn't meet that criteria. But if we take the average of those 2 years for him, he is a 3.5 which would make him a core performer. That's the logic I also want to build into this where I'm struggling. Other examples, Donald duck sums 4, but his average is 2 which makes him a low performer.

I want the first piece to be as is as its own column, but then I also want another column with the second piece included too as we may need to look at it differently. Does this make sense?

Thank  you!!

Super User

## Re: Switch & Calculate Issue

You just have to work this until you get your logic correct.

For example, by your stated rules, Donald Duck gets an N/A because his FY17 score is NOT less than 3.

```Performance? =
VAR Rating = SWITCH(
TRUE(),
[FY16]>=4 && [FY17]>=4,"High Performer",
([FY16]>=3 && [FY16] <= 3.5) && ([FY17] >= 3 && [FY17]<=3.5),"Core Performer",
[FY16] < 3 && [FY17] < 3,"Low Performer",
"N/A"
)
RETURN Rating```

For your second one, maybe something like this:

```Performance2 =
VAR Score = [FY17]+[FY16]/2
VAR Rating = SWITCH(
TRUE(),
Score>=4,"High Performer",
Score>=3 && Score <= 3.5,"Core Performer",
Score < 3,"Low Performer",
"N/A"
)
RETURN Rating```

These formulas work for their defined rules, but you will have to edit in your own rules.

Proud to be a Datanaut!

Member

## Re: Switch & Calculate Issue

Thanks @Greg_Deckler. I didn't think to do it like this. I was clearly overcomplicating it! The "VAR" feature is really helpful. Thank you so much!

Member

## Re: Switch & Calculate Issue

@Greg_Deckler, quick question...

I have written the query the way I need to which is:

Performance =
VAR Score = (RELATED('Rating-FY17'[FY17 Rating])+RELATED('Rating-FY16'[FY16 Rating]))/2
VAR Rating = SWITCH(
TRUE(),
Score = 5, "Top Performer",
Score >= 4 && Score < 5,"High Performer",
Score >= 3 && Score < 4,"Base Performer",
Score > 1 && Score < 3,"Low Performer",
"N/A"
)
RETURN Rating

However, I need to build in a fail safe where if FY16 or FY17 has a blank value in either then "N/A" because there isn't enough data to give a fair performance rating category. I accounted for if the value is less than 1 then "N/A" but, if for example it looks like this I would want it to say "N/A" as well:

Name                         Performance                   FY17 Rating             FY16 Rating
Jack Skellington                 N/A                               4                               0
Mary Poppins                     N/A                               0                               4

Super User

## Re: Switch & Calculate Issue

Perhaps:

```Performance =
VAR Score = (RELATED('Rating-FY17'[FY17 Rating])+RELATED('Rating-FY16'[FY16 Rating]))/2
VAR Rating = SWITCH(
TRUE(),
Score = 5, "Top Performer",
Score >= 4 && Score < 5,"High Performer",
Score >= 3 && Score < 4,"Base Performer",
Score > 1 && Score < 3,"Low Performer",
"N/A"
)
RETURN IF(ISBLANK(RELATED('Rating-FY17'[FY17 Rating])) || ISBLANK(RELATED('Rating-FY16'[FY16 Rating])),"NA",Rating)```

Proud to be a Datanaut!

Member

## Re: Switch & Calculate Issue

OMG is it really that simple? I was literally about to write that. Thank you so much.

Super User

## Re: Switch & Calculate Issue

Sometimes there's no need to overcomplicate or overthink things!