Forum Discussion
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 RatingThese formulas work for their defined rules, but you will have to edit in your own rules.
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 RatingNot 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?
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!!
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 RatingThese formulas work for their defined rules, but you will have to edit in your own rules.
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!
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 RatingHowever, 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