Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Average and Sum from same column and list

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

Need Help

4 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

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

        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