I have Table 1 with the following fields:
- Question
- Correct Answer
- Response (possible responses that were chosen, there is one correct answer with multiple incorrect for each question)
- Incorrect (no. of candidates with incorrect answer)
- Correct (no. of candidates with correct answer)
- Grand Total (total no. of candidates that answered particular question)
- % Incorrect for each response (no. of Incorrect for each response/Grand Total)*100
I would like to create Table 2 with the following fields:
- Question
- Correct Answer
- Most frequently chosen incorrect answer (answer that has max no. of incorrect candidates)
- Overall % of incorrect candidates (total Incorrect/Grand Total)
Please see sample tables attached.
I've tried using the "Summarize" tool to:
- Group by Question
- Group by Correct Answer
- Group by Response
- Max Incorrect
- Group by Grand Total
I should be getting only the response that has the max no. of incorrect candidates in the "Most frequently chosen incorrect answer" column so that I am getting one row per Question.
Instead, I am still getting multiple rows per question and all the responses are showing rather than just the one with the most frequently chosen incorrect one.
Appreciate any bit of help!
