Highest score based on 2 variables

[hans263]

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. 

[hans263]

Yep. This works great! Many thx. 

[tamerj1 · Accepted solution]

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]

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]

@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]

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 %.  

[tamerj1]

@hans263 
See attached modified file

% of Highest Score = 
VAR Numerator = [Count of Highest Scores]
VAR Denominator = 
    COUNTROWS ( 
        CALCULATETABLE ( 
            VALUES ( 'Table'[Staffmember] ), 
            ALLEXCEPT ( 'Table', 'Table'[Department] ) 
        ) 
    )
VAR Result = DIVIDE ( Numerator, Denominator )
RETURN
    Result