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] )
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))Not following you. I agree something more powerful then writing out each column name, having the ability to reference column names using wildcards, survey results, would prove benefitial.
What do others do/think?
Jeremy
jdugas who is "you" you are not following?
