Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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?
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:
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 Rating
These 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 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
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)
OMG is it really that simple? I was literally about to write that. Thank you so much.
🙂
Sometimes there's no need to overcomplicate or overthink things!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |