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.
Anyone? 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 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!