Forum Discussion
Sum based on Different Column
Hello,
People here have given great ideas. This question is complicated. Maybe there is no easy answer. You can try this.
- Add an index to the table in Query Editor.
- Choose three columns with CTRL. Then click Unpivot Columns.
- Create a measure.
Average =
SUM ( Table1[Value] ) / COUNT ( Table1[Value] )
What I would suggest doing is to rather create your column in the Query Editor.
In the Query Editor it has got a lot more functionality in order to create the column you require.
And then once you have your new column created you can very easily create your measure.
If you need some assistance, can you provide a sample of data?
Not following you.
Sample data
Date Answer1 Answer2 Answer3
1/1/2017 4 4 5
1/1/2017 3 4 0
The above example would create a new calculated column which would sum up the columns "Answer1, Answer 2, Answer3" perhaps using a wildcard like "Answer?"
Hope that makes sense.
Jeremy
hi jdugas
Do your columns always have Answer1, Answer2, Answer3 etc?
If so you can create the following measure.Answer Total = sum('TableName'[Answer1]) + sum('TableName'[Answer2]) + sum('TableName'[Answer3])A Power Query (query editor) solution would be to add a column with the following code:
= List.Sum(List.Transform(List.Zip({Record.FieldNames(_),Record.FieldValues(_)}), each if Text.Start(Text.Upper(_{0}),6) = "ANSWER" then _{1} else 0))
What would you give for a survey result average?
Record 1 = (4+4+5)/3= 4.33
Record 2 = (3+4+0)/2= 3.5
Answer1 average = 7/2 or 3.5
Answer2 average = 8/2 or 4
Answer3 = Since there's only one score given, 5 is the answer. Zero (0) is considered a "null" values and will not be considered answered.
And how would you write it out in DAX?
