Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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.

     

     

     

9 Replies

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Greg_Deckler,

         

        Here is an example of the data: 

         

        EE IDNameFY17 RatingFY16 RatingPerformer?
        1234Skellington, Jack44High
        2345Pooh, Winnie43Core
        3456Mouse, Mickey52Core
        4567Duck, Donald13Low

         

         

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