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
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?
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] )