Calculate Score

[Researcher93]

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_answer	Region	DateAnswer	Departement	Qestion	Answer
id1	xxx	xxxx	xxxx	N1	OK
id1	xxx	xxxx	xxxx	N1	OK
id1	xxx	xxxx	xxxx	N1	OK
id1	xxx	xxxx	xxxx	N2	OK
id1	xxx	xxxx	xxxx	N3	OK
id1	xxx	xxxx	xxxx	N4	OK
id1	xxx	xxxx	xxxx	N4	OK
id2	xxx	xxxx	xxxx	N1	OK
id2	xxx	xxxx	xxxx	N1	KO
id2	xxx	xxxx	xxxx	N1	OK
id2	xxx	xxxx	xxxx	N2	OK
id2	xxx	xxxx	xxxx	N3	OK
id2	xxx	xxxx	xxxx	N4	OK
id2	xxx	xxxx	xxxx	N4	OK
id3	xxx	xxxx	xxxx	N1	OK
id3	xxx	xxxx	xxxx	N1	OK
id3	xxx	xxxx	xxxx	N1	OK
id3	xxx	xxxx	xxxx	N2	KO
id3	xxx	xxxx	xxxx	N3	OK
id3	xxx	xxxx	xxxx	N4	OK
id3	xxx	xxxx	xxxx	N4	OK

[v-stephen-msft · Accepted solution]

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.