Average and Sum from same column and list

[Anonymous]

I have a list of items in a colum that I am summing results from another column.  Problem is I want to take some of them and average the responses and sum the rest.  Meaning, results come back as:

Response 1

Response 2

Response 3

Response 4

Response 5

Response 6

Response 7

Response 8

Response 9

Response 10

I want to get the average of 1 2 3 together as one answer but still sum the remainder.

Response 1 2 3 Average

Response 4 Sum

Response 5 Sum

Response 6 Sum

Response 7 Sum

Response 8 Sum

Response 9 Sum

Response 10 Sum

Hope this helps and can be done in one chart.  Thanks

[v-danhe-msft]

Hi @Anonymous,

Based on my test, you could refer to below steps:

Create a calculated column in your row table:

Column = IF([Item]="Response 1"||[Item]="Response 2"||[Item]="Response 3",CALCULATE(AVERAGE(Table1[Value]),FILTER('Table1',[Item]="Response 1"||[Item]="Response 2"||[Item]="Response 3")),[Value])

Create a new table and a new calculated column in your new table:

New table:

New Table = SUMMARIZE('Table1','Table1'[Column],"t",CALCULATE(MAX('Table1'[Item])))

Calculated column:

Column 2 = SUBSTITUTE('New Table'[t],"Response 3","Response Average")

Result:

You could also downlaod the pbix file to have a view.

https://www.dropbox.com/s/1le326q1t4clfhs/Average%20and%20Sum%20from%20same%20column%20and%20list.pbix?dl=0

Regards,

Daniel He

[Anonymous]

Thank you very much Daniel.  It seems to have started me on the right path but when I created the second table New Tabl, then tried to compare the two, it cut out many of my original responses.  I downloaded your file and took a look at it and I know I have some differences in my table set up and all it brought back some but not all.  Please advise.  Thanks

[v-danhe-msft]

Hi @Anonymous,

From your description, I could not understand the setence " it cut out many of my original responses" post in your problem, could you have created relationships between the new table you have created with the other tables? Due to I could not reproduce your problem, could you please share the pbix file is possible and post your desired result?

Regards,

Daniel He

[Anonymous]

A lot of my original results are missing from the pull.  When I say cut out it was not displaying many results that were there originally.