Forum Discussion

hans263's avatar
hans263
New Member
4 years ago
Solved

Highest score based on 2 variables

Hi experts, I have an issue regarding a table with staffmembers, training they followed and the scores given for each training. Table looks like this: The result should be that for each depar...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi hans263 
    Please refer to attached sample file with the solution

    Count of Highest Scores = 
VAR CurrentScore = SELECTEDVALUE ( 'Table'[TrainingTypescore] )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( 'Table'[Staffmember] ),
            CALCULATE ( MAX ( 'Table'[TrainingTypescore] ), ALL ('Table'[NameTrainingFollowed], 'Table'[TrainingTypescore] ) )
                = CurrentScore
        )
    ) + 0
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi hans263 
Please refer to attached sample file with the solution

Count of Highest Scores = 
VAR CurrentScore = SELECTEDVALUE ( 'Table'[TrainingTypescore] )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( 'Table'[Staffmember] ),
            CALCULATE ( MAX ( 'Table'[TrainingTypescore] ), ALL ('Table'[NameTrainingFollowed], 'Table'[TrainingTypescore] ) )
                = CurrentScore
        )
    ) + 0
  • hans263's avatar
    hans263
    New Member
    4 years ago

    Additionale question: Current Score holds the maximum of found results. If I want to use that value, how can I get this back into a Column? Or otherwise, the number of unique staff members for echt department. OPS=1, Sales=3

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      hans263 
      Modified file attached

      Count of Highest Scores = 
SUMX ( 
    VALUES ( 'Table'[NameTrainingFollowed] ),
    CALCULATE (
        VAR CurrentScore = SELECTEDVALUE ( 'Table'[TrainingTypescore] )
        RETURN
            COUNTROWS (
                FILTER (
                    VALUES ( 'Table'[Staffmember] ),
                    CALCULATE ( MAX ( 'Table'[TrainingTypescore] ), ALL ('Table'[NameTrainingFollowed], 'Table'[TrainingTypescore] ) )
                        = CurrentScore
                )
            ) + 0
    )
)
      • hans263's avatar
        hans263
        New Member
        4 years ago

        Awesome. Last question. I finaly want to show the response. So in fact Count of Highest Score devided by the total number of staff members for a give department in %.