Forum Discussion
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 ...
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?