Sum based on Different Column

[jdugas]

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.

[GilbertQ]

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?

[jdugas]

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

[jdugas]

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.

[jdugas]

And how would you write it out in DAX?

[v-jiascu-msft · Accepted solution]

@jdugas

Hello,

People here have given great ideas. This question is complicated. Maybe there is no easy answer. You can try this.

1. Add an index to the table in Query Editor.
2. Choose three columns with CTRL. Then click Unpivot Columns.
3. Create a measure.

Average =

SUM ( Table1[Value] ) / COUNT ( Table1[Value] )

 

[v-jiascu-msft]

@jdugas

Hi,

Could you tell me if this worked? What else can I do for you?

Best Regards!

Dale

[jdugas]

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.

[GilbertQ]

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

[MarcelBeug]

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

[jdugas]

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

[MarcelBeug]

@jdugas who is "you" you are not following?