How to get count of rows and average of data at the bottom of matrix? (Data is dynamic)

[Rishabh03]

 I have requirement in which I need to get the count of correct data and average of it, below is the sample which I need to achieve, I have highligted the part in which I need help.

[v-eqin-msft]

Hi @Rishabh03 ,

Sorry, to my knowledge, it's not possible two add additional rows in Matrix. So I choose to do it in Table visual before.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Rishabh03 ,

Is there another table that identify the correct answers for questions as shown below?

If so, I'd suggest you add columns to such table:

No. =
SWITCH (
    [Question],
    "Question 1",
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( 'Table', [Question 1] = EARLIER ( Answers[Answer] ) )
        ),
    "Question 2",
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( 'Table', [Question 2] = EARLIER ( Answers[Answer] ) )
        ),
    "Question 3",
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( 'Table', [Question 3] = EARLIER ( Answers[Answer] ) )
        ),
    "Question 4",
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( 'Table', [Question 4] = EARLIER ( Answers[Answer] ) )
        )
) + 0

Assessment Average = [No.] /  COUNTROWS('Table') 

 

Or then create a new table since you want add new rows, and it will be a little complex:

New Table =
VAR _t2 =
    ROW (
        "Student Name", BLANK (),
        "Question 1", LOOKUPVALUE ( Answers[No.], Answers[Question], "Question 1" ),
        "Question 2", LOOKUPVALUE ( Answers[No.], Answers[Question], "Question 2" ),
        "Question 3", LOOKUPVALUE ( Answers[No.], Answers[Question], "Question 3" ),
        "Question 4", LOOKUPVALUE ( Answers[No.], Answers[Question], "Question 4" )
    )
VAR _t3 =
    ROW (
        "Student Name", BLANK (),
        "Question 1", LOOKUPVALUE ( Answers[Assessment Average], Answers[Question], "Question 1" ),
        "Question 2", LOOKUPVALUE ( Answers[Assessment Average], Answers[Question], "Question 2" ),
        "Question 3", LOOKUPVALUE ( Answers[Assessment Average], Answers[Question], "Question 3" ),
        "Question 4", LOOKUPVALUE ( Answers[Assessment Average], Answers[Question], "Question 4" )
    )
RETURN
    UNION ( 'Table', _t2, _t3 )

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

[Rishabh03]

Hi @v-eqin-msft  Thanks for the repsonse, questions are not fixed those are dynamic, question count can increase or decreased based on selection of class. I am using matrix, solution which you have provide is for table, it will be great if you can help me in achieving this in matrix.

[Arul]

@Rishabh03 ,

If you could share your sample data that would be helpful to give the solution.

[Rishabh03]

Thanks @Arul  for the response, not possible for me to share the data, but for your information, I have data which are displaying are the multiple choice question answer, correct answer are in green and wrong answer are displaying as a red in color, in last two rows I have to get the sum of all the correct answer and the average of it.