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 department the number of maximimum(highest) scores are returned (highest score for each staffmember), and also a sum of maximum scores per department. I have used RANKX but there is an issue regarding strings and integerrs. Very anxious to see what solutions are available. 

  • 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

6 Replies

  • 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
    )
)