Forum Discussion

Researcher93's avatar
Researcher93
New Member
3 years ago
Solved

Calculate Score

Hello comunity,

I have a table that looks like the table below. In this table we have questions (N1, N2, N3 and N4) of which N1 has 3 questions, N2 one question, N3 one question and N4 2 questions.
The answers are noted as follows:
a good answer to a question:
N1 is worth 0.33 (correct answers to the 3 questions N1 are worth 1).
N2 is 1
N3 is 1
N4 is worth 0.5 (correct answers to the 2 questions N4 are worth 1).

I would like to calculate the score by using the method below:

1. If all three criteria of N1 OK, score=1. If only one OK, score=0.33. If only two OK, score=0,66. (despite results of higher levels criteria -L2,L3 and L4)

2. If score=1, take into account criteria N2, if OK, score=2, if not score=1. (despite results of higher levels criteria -L3 and L4)

3. If score = 2 take into account criteria N3, if OK, score =3, if not score =2. (despite results of higher levels criteria -L4)

4. If score=3, take into account criteria N4, if both OK, score =4, if only one OK, score 3.5, if both KO, score 3.

 

Id_answerRegionDateAnswerDepartementQestionAnswer
id1xxxxxxxxxxxN1OK
id1xxxxxxxxxxxN1OK
id1xxxxxxxxxxxN1OK
id1xxxxxxxxxxxN2OK
id1xxxxxxxxxxxN3OK
id1xxxxxxxxxxxN4OK
id1xxxxxxxxxxxN4OK
id2xxxxxxxxxxxN1OK
id2xxxxxxxxxxxN1KO
id2xxxxxxxxxxxN1OK
id2xxxxxxxxxxxN2OK
id2xxxxxxxxxxxN3OK
id2xxxxxxxxxxxN4OK
id2xxxxxxxxxxxN4OK
id3xxxxxxxxxxxN1OK
id3xxxxxxxxxxxN1OK
id3xxxxxxxxxxxN1OK
id3xxxxxxxxxxxN2KO
id3xxxxxxxxxxxN3OK
id3xxxxxxxxxxxN4OK
id3xxxxxxxxxxxN4OK
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Researcher93 ,

     

    You may calculate 4 measures.

     

    N1Score =
ROUNDDOWN (
    DIVIDE (
        CALCULATE (
            COUNT ( 'Table'[Qestion] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Id_answer] = MAX ( 'Table'[Id_answer] )
                    && [Qestion] = "N1"
                    && [Answer] = "OK"
            )
        ),
        3
    ),
    2
)

    N2Score =
IF (
    [N1Score] = 1,
    IF (
        CALCULATE (
            COUNT ( 'Table'[Qestion] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Id_answer] = MAX ( 'Table'[Id_answer] )
                    && [Answer] = "OK"
                    && [Qestion] = "N2"
            )
        ) = 1,
        2,
        1
    )
)

     

    N3Score = 
IF (
    [N1Score] = 1
        && [N2Score] = 2,
    IF (
        CALCULATE (
            COUNT ( 'Table'[Qestion] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Id_answer] = MAX ( 'Table'[Id_answer] )
                    && [Answer] = "OK"
                    && [Qestion] = "N3"
            )
        ) = 1,
        3,
        2
    )
)

    N4Score = 
IF (
    [N1Score] = 1
        && [N2Score] = 2
        && [N3Score] = 3,
    IF (
        CALCULATE (
            COUNT ( 'Table'[Qestion] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Id_answer] = MAX ( 'Table'[Id_answer] )
                    && [Answer] = "OK"
                    && [Qestion] = "N4"
            )
        ) = 1,
        4,
        3.5
    )
)

     

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Researcher93 ,

     

    You may calculate 4 measures.

     

    N1Score =
ROUNDDOWN (
    DIVIDE (
        CALCULATE (
            COUNT ( 'Table'[Qestion] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Id_answer] = MAX ( 'Table'[Id_answer] )
                    && [Qestion] = "N1"
                    && [Answer] = "OK"
            )
        ),
        3
    ),
    2
)

    N2Score =
IF (
    [N1Score] = 1,
    IF (
        CALCULATE (
            COUNT ( 'Table'[Qestion] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Id_answer] = MAX ( 'Table'[Id_answer] )
                    && [Answer] = "OK"
                    && [Qestion] = "N2"
            )
        ) = 1,
        2,
        1
    )
)

     

    N3Score = 
IF (
    [N1Score] = 1
        && [N2Score] = 2,
    IF (
        CALCULATE (
            COUNT ( 'Table'[Qestion] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Id_answer] = MAX ( 'Table'[Id_answer] )
                    && [Answer] = "OK"
                    && [Qestion] = "N3"
            )
        ) = 1,
        3,
        2
    )
)

    N4Score = 
IF (
    [N1Score] = 1
        && [N2Score] = 2
        && [N3Score] = 3,
    IF (
        CALCULATE (
            COUNT ( 'Table'[Qestion] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Id_answer] = MAX ( 'Table'[Id_answer] )
                    && [Answer] = "OK"
                    && [Qestion] = "N4"
            )
        ) = 1,
        4,
        3.5
    )
)

     

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.