Aggregating fields and selecting the one with the max

[r-alrahmani]

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:

1. Group by Question
2. Group by Correct Answer 
3. Group by Response
4. Max Incorrect
5. 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!

[cjaneczko]

Use a summarize tool and group by the Question and Max Incorrect %. Join that back to the Original table.

[kamal03 · Accepted solution]

Hi @r-alrahmani 

You can achieve this by using 2 summarize tools and 2 join tools.

Please check the workflow and please let me know if it worked for you or not.

[r-alrahmani]

Hello!

Thanks for the quick reply!

At first, it was still giving me multiple rows per question, but I basically used your solution with an additional filter where (Correct Answer ! = Response) and it gave me the desired output :) thanks again!!