Build a flow to get response from a form, then calculate each question (score each question) based on the form response using PowerAutomate, finally calculate the totals.

[lilykong]

Hi MS Power Automate Experts! 

I am really new to Power Automate tool and have successfully built couple Power flows by following this community. 

I am now having some issue building a flow that I have no idea how to do so, it definitely needs expression and compose with logics and codes, I can described what I am trying to do here: 

Background:

I created a scorecard MS Form, trying to use the form to eventually calculate the total scores based on the answers from the questions on the form. 

For example, I have total of 5 questions on the form, each question would consider a score/weight:

• Q1 = 5
• Q2 = 4
• Q3 = 3
• Q4 = 2
• Q5 = 1

Then, each question has 4 options and each option has a score:

• Option 1 = 10
• Option 2 = 6
• Option 3 = 3
• Option 4 = 0

All the questions are mandatory so we will for sure has an answer from each question. 

The Power Flow I am trying to do is:

1. Trigger: When a new response is submitted: scorecard form; (Done)
2. Get response details: scorecard form, based on Reponses ID;  (Done)
3. Calculate each question + answer (Not sure what to do, assuming using "Compose")
4. Create a list in SharePoint with the Question title, provide the Score of each question as the content instead of the answers from the form. 
5. Generate totals for each response ID and provide that total to another List based on the Response ID number. 

The compose expression is something I assume can work, but I have no idea how to build the logic/code.

Here is an example: 

If Q1's answer is Option 2, then 5 x 6 = 30;

If Q2's answer is Option 3, then 4 x 3 = 12; 

If Q3's answer is Option 4, then 3 x 0 = 0; 

If Q4's answer is Option 1, then 2 x 10 = 20; 

If Q5's answer is Option 2, then 1 x 2 = 2; 

Note, the questions and answers from the form are all text based, for example:

• Q1: How many users are using this solution?
  • Option 1: All employees
  • Option 2: Employees from couple business units.
  • Option 3: Employees from one business unit.
  • Option 4: Only one person. 

Then based on the calculations, build a list in SharePoint to display the values of each question: 

Response ID	Q1	Q2	Q3	Q4	Q5
001	30	12	0	20	2

Can anyone help me with this flow? Or is this something we can achieve via Power Automate? 

Please advise. 

Thank you so much! 

Lily Kong

[lilykong]

To specify the logic I am trying to build:

If Option 1 of Q1 is selected, then 5 x 10;

ElseIf Option 2 of Q1 is selected, then 5 x 6;

ElseIf Option 3 of Q1 is selected, then 5 x 3;

ElseIf Option 4 of Q1 is selected, then 5 x 0;

If Option 1 of Q2 is selected, then 4 x 10;

ElseIf Option 2 of Q2 is selected, then 4 x 6;

ElseIf Option 3 of Q2 is selected, then 4 x 3;

ElseIf Option 4 of Q2 is selected, then 4 x0;

and so forth. ..

ElseIf Option 1 of Q1 is selected, then 5 x 6;

[RobElliott · Accepted solution]

@lilykong there are probably other ways to do it, bit in any case it's quite a long flow as all the possible combinations of questions and options have to be evaluated and multiplied.

In the form I have 5 questions, each of which branch to a follow-on question. For ease each of the follow-on questions in my example are the same but they don't have to be.

1. In the flow add the trigger and get items action as normal:

2. Next, add 5 float variables. I've named them varQ1, varQ2 etc. Add the score attached to each one as the  value.

3. Add 4 float variables for the options. I've named the O1, O2 etc. Add the score attached to each option as the value.

The reason for adding the initial values in the variables is so that if you decide to change the score for a question or an option you only need to do it in 1 place.

4. Next, add 5 more variables, this time array variables. I've named them varQ1Total, varQ2Total etc. Leave the vaue empty.

5. Next for each question you need to add a switch control which is type of condition that looks at a question and can do actions depending on what answer has been selected. So below we've got a switch on "Which Electric car car would you buy?" It will add 1 initial "Case" and you need to add 3 more (from the + next to the first one) for each of the other 3 options. In any switch you can have up to 25 cases.

Enter the answer in the Equals field - it must be exactly the spelling and case from the form.

In each case add a compose action and from the Expression tab of the dynamic content box add the expression mul(variables('varQ1'), variables('varO3')) which, in this case multiplies the varQ1 variable with the varO3 variable.

Still in the same case add an update array variable action, select varQ1Total and add the expression int(outputs('Q1_3')) which appends the total for that case to the varQ1Total variable. DO that for each of the 4 option cases for this question), changing the variables to the correct ones.

6.  Add a switch and cases in the same way for each of the other 4 questions.

Next, add 5 compose actions. I've named them Result Q1, Result Q2 etc. Into each one add the following expression max(variables('varQ1Total')) changing the variable to varQ2Total etc.

7. Finally, add a SharePoint create item action. Select your site and list and in the Title column select Response Id. Then for each of number type Q1, Q2.... columns add the expression int(outputs('Result_Q1'))

The result is shown below. In my example for Response ID 6 the scores were calculated from the form answers as follows:

Q1 Option 1 = 5 x 10 = 50

Q2 Option 4 = 4 x 0 = 0

Q3 Option 3 = 3 x 3 =9

Q4 Option 4 = 2 x 0 = 0

Q5 Option 2 = 1 x 6 =6

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.