Switch & Calculate Issue

[Anonymous]

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!

[Anonymous]

Anyone? @Greg_Deckler? 

[Greg_Deckler]

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?

[Anonymous]

Hi @Greg_Deckler,

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

[Greg_Deckler · Accepted solution]

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.

[Anonymous]

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!

[Anonymous]

@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  

[Greg_Deckler]

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)

[Anonymous]

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

[Greg_Deckler]

🙂

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