Forum Discussion
Solved
Sum based on Different Column
Is it possible to create a calculated column to sum all column names that begin with "ANSWERx" where "x" is either a single digit or double digit? I'm trying to find a score for survey results.
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] )
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?