Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
Hello,
People here have given great ideas. This question is complicated. Maybe there is no easy answer. You can try this.
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
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.
Average =
SUM ( Table1[Value] ) / COUNT ( Table1[Value] )
Hi,
Could you tell me if this worked? What else can I do for you?
Best Regards!
Dale
I honestly can't remember the exact reason or details on how to make this work but I accomplished this by using the "unpivot columns" function in power query. I think that's what you call it.
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?
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |