Single score dashboard using a SharePoint list with a yes/no answers

[Anonymous]

I’m trying to build a single score dashboard using a SharePoint list of 14 questions in columns  with a yes/no answers in rows. If they comply with all 14, then they will be given a score of “5” and in they failed to follow more than 3 out of the 14 then they were given a score of “1”. See breakdown below:

# of non-conformances	Score
0	5
1	4
2	3
3	2
>3	1

Can Anyone assist me?

[v-juanli-msft]

Hi @Anonymous 

Assume you have example data below:

Create measures

no numbers =
VAR n =
    CALCULATE (
        COUNT ( 'Table'[question id] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[name] = MAX ( 'Table'[name] )
                && 'Table'[result] = "no"
        )
    )
RETURN
    IF ( ISBLANK ( n ), 0, n )

score =
SWITCH (
    TRUE (),
    [no numbers] = 0, 5,
    [no numbers] = 1, 4,
    [no numbers] = 2, 3,
    [no numbers] = 3, 4,
    [no numbers] > 3, 1
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Team,

Sorry but i'm not a tec savy person however the list has the questions in colums and the answers in rows like below:

NAME

Q1

Q2

Q3

Q4

Q5

Q6

Q7

Q8

Q9

Q10

Q11

Q12

Q13

Q14

John

no

no

no

yes

yes

yes

yes

yes

yes

yes

yes

yes

yes

yes

Jack

no

yes

yes

yes

yes

yes

yes

yes

yes

yes

yes

yes

yes

yes

the result anticipated is:

Name	Score
John	2
Jack	4

Any taughts

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

In Edit queries,

Select "NAME" column, "Unpivot other columns",

Rename "Attribute", "question" as "Value", "answer", Close&&apply,

Create measure

no numbers = 
VAR n =
    CALCULATE (
        COUNT ( 'Table'[question] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[NAME] = MAX ( 'Table'[NAME] )
                && 'Table'[answer] = "no"
        )
    )
RETURN
    IF ( ISBLANK ( n ), 0, n )

score = 
SWITCH (
    TRUE (),
    [no numbers] = 0, 5,
    [no numbers] = 1, 4,
    [no numbers] = 2, 3,
    [no numbers] = 3, 2,
    [no numbers] > 3, 1
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.