Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdugas
Helper I
Helper I

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.

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

Sum based on Different Column01.jpg

 

 Sum based on Different Column02.jpg

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
GilbertQ
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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?

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

Sum based on Different Column01.jpg

 

 Sum based on Different Column02.jpg

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@jdugas

 

 

Hi,

 

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

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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))
Specializing in Power Query Formula Language (M)

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?

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.