Forum Discussion

jdugas's avatar
jdugas
Icon for Advocate I rankAdvocate I
9 years ago
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.
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

jdugas

 

 

Hi,

 

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

 

 

Best Regards!

Dale

  • jdugas's avatar
    jdugas
    Icon for Advocate I rankAdvocate I
    9 years ago

    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.