Join Columns with Rows

[charlyrosero]

Hi

the main idea it's show the Total Weight  by question in a survey. the survey  table there are ID questions  (columns) with values 1 (means the participant answered Yes) and 0 (means that the participat answered No). for the other hand the table weitghtQuestion with the fields idquestion, weight. the point it's that the last table save the values in rows.

Survey Table

idParticipant	Question_1	Question_2	Question_3	Question_4
1	1	1	0	0
2	0	1	0	1
3	1	0	1	1
4	0	1	0	1

Weight Question Table

idQUestion	Weight
Question_1	10
Question_2	5
Question_3	15
Question_4	20

Result

Question	Total Weight
Question_1	20	(10 + 10)
Question_2	15	(5 + 5 + 5)
Question_3	15	(15)
Question_4	60	(20 + 20 + 20)

thank you s much for the help

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[charlyrosero]

Hi @Ashish_Mathur , @Nathaniel_C  Thank you so much. your help was very useful. 

@Ashish_Mathur this solution lets to work with any questions and any weight questions. 

[Ashish_Mathur]

You are welcome.

[Nathaniel_C]

Hi @charlyrosero ,

Try this: will post the pbix in a couple.

Total Weight = 
var _Q1 = Sum(Survey[Question_1] ) * MAX('Weight'[Question_1])
var _Q2 = Sum(Survey[Question_2] ) * MAX('Weight'[Question_2])
var _Q3 = Sum(Survey[Question_3] ) * MAX('Weight'[Question_3])
var _Q4 = Sum(Survey[Question_4] ) * MAX('Weight'[Question_4])

var _Sw = SWITCH(TRUE(),
           MAX(Weights[IdQUestion]) = "Question_1", _Q1,
           MAX(Weights[IdQUestion]) = "Question_2", _Q2,
           MAX(Weights[IdQUestion]) = "Question_3", _Q3,
           MAX(Weights[IdQUestion]) = "Question_4", _Q4)


return _Sw

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Hi @charlyrosero ,

PBIXWeighted Survey

I used both your tables, but the weighted table I created a copy and transposed it so we could get MAX for each value. Let me know if you need to know anything about transposing and duplicating in Power Query.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[charlyrosero]

Hi  @Nathaniel_C !
thank you. great solution. I only have one question. there are 80 questions and 80 weights. is there  a dynamic solution for do not create each one measure?

[Nathaniel_C]

Hi @charlyrosero ,

Let me think about that.  I just did the first line, and copied and pasted the rest. Then changed the numbers.  It was pretty quick.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel