Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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-conformancesScore
05
14
23
32
>31

 

Can Anyone assist me?

Need Help
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

     

     

     

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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:

       

      NAMEQ1Q2Q3Q4Q5Q6Q7Q8Q9Q10Q11Q12Q13Q14
      Johnnononoyesyesyesyesyesyesyesyesyesyesyes
      Jacknoyesyesyesyesyesyesyesyesyesyesyesyesyes

       

      the result anticipated is:

       

      NameScore
      John2
      Jack4

       

      Any taughts

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.