Weighted Average with Text Values

[Anonymous]

I have a form where submissions include two questions with 5 important levels and 5 options in each, and I want to see the weighted average of each option within each question

Question 1: What is most important to you?

Options: Support, Documentation, Quality, Patience, Speed

Most Important | Important | Neutral | Not Important | Least Important

Question 2: What is most important to you?

Options: Communication, Professionalism, Activity, Completion, Punctuality

Importance Most Important | Important | Neutral | Not Important | Least Important

Results table

SubmissionDate	Question1A	Question1B	Question1C	Queston1D	Queston1E	Question2A	Question2B	Question2C	Queston2D	Question2E
1/1/17	Support	Documentation	Patience	Speed	Quality	Communication	Activity	Completion	Punctuality	Professionalism
1/4/17	Quality	Support	Documentation	Patience	Speed	Activity	Communication	Punctuality	Professionalism	Completion
1/9/17	Speed	Support	Quality	Documentation	Patience	Punctuality	Completion	Communication	Professionalism	Activity
1/15/17	Support	Quality	Patience	Speed	Documentation	Activity	Completion	Professionalism	Communication	Punctuality

At the end of the day, I am trying to create a table that changes it's order based on the weighted average score.

Support	Documentation	Quality	Patience	Speed
3.8	3.2	2.3	2.1	1.8

I created an inline load for the two option sets:

Dim1:

LOAD * INLINE [

  Dim1

  Speed

  Documentation

  Patience

  Support

  Quality

];

Dim2:

LOAD * INLINE [

    Dim2

    Communication

    Activity

    Completion

    Punctuality

    Professionalism

];

In the table, the dimension is set as 'Dim1' and there are two expressions.

1. Unknown

2. RowNo()

Best expression I could come up with was something like this which didn't work.

If(Dim1='Speed',sum(if(Question1A='Speed',5,

                    if(Question1B='Speed',4,

                    if(Question1C='Speed',3,

                    if(Question1D='Speed'=2,

                    if(Question1E='Speed',1,0)))))

Thank you for the help,

Phil

[Anonymous]

Thank you for the help, this worked out great!  I haven't figured out mapping and its many use cases.