cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SirGalatsi Member
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
Highlighted
Super User
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.

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


9 REPLIES 9
SirGalatsi Member
Member

Re: Switch & Calculate Issue

Anyone? @Greg_Deckler

Super User
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?

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


SirGalatsi Member
Member

Re: Switch & Calculate Issue

Hi @Greg_Deckler,

 

Here is an example of the data: 

 

EE IDNameFY17 RatingFY16 RatingPerformer?
1234Skellington, Jack44High
2345Pooh, Winnie43Core
3456Mouse, Mickey52Core
4567Duck, Donald13Low

 

 

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!!

 

Highlighted
Super User
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.

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


SirGalatsi Member
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!

SirGalatsi Member
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
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)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


SirGalatsi Member
Member

Re: Switch & Calculate Issue

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

Super User
Super User

Re: Switch & Calculate Issue

Smiley Happy

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!