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

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

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

    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