Average of column by a value

[wcd1213]

Hi All,

I have a table with six columns. Column 1 and 2 are identifiers labeled by person and response number. Columns 3-5 are the scores that this person received and column 6 is the average across the three rows. I need to find a way to get the average of Q1-Q3 AVG split up by the name of the person. 

Here is the table from excel. 

Instructor	Response #	Q1	Q2	Q3	Q1-Q3 AVG
Person1	1	5	4	2	3.67
Person1	2	5	5	5	5.00
Person1	3	3	4	4	3.67
Person1	4	1	1	3	1.67
Person1	5	5	5	5	5.00
Person1	6	5	3	1	3.00
Person1	7	5	4	2	3.67
Person2	1	5	5	5	5.00
Person2	2	3	5	4	4.00
Person2	3	4	2	3	3.00
Person2	4	5	4	5	4.67
Person2	5	5	3	1	3.00
Person2	6	1	4	3	2.67
Person2	7	4	5	5	4.67
Person3	1	2	4	4	3.33
Person3	2	3	3	3	3.00
Person3	3	5	5	5	5.00
Person3	4	4	3	1	2.67
Person3	5	4	4	3	3.67
Person3	6	5	5	2	4.00
Person3	7	3	3	3	3.00

For example rows 1-7 in the new column should be 3.67. 

(3.67 + 5 + 3.67 + 1.67 + 5 + 3 + 3.67) / 7 = 3.67

Please let me know if there is a specific formula to acheive this. Any help is appreciated. 

[Avantika-Thakur]

Hi @wcd1213 ,

You can try creating this measure - 

CALCULATE(AVERAGE('Table'[Q1-Q3 AVG]),ALLEXCEPT('Table','Table'[nstructor]))

Hope this helps!

Please accept the solution if this answers your query.

Thanks,

Avantika

[wcd1213]

Hi @Avantika-Thakur ,

Thank you for your response. 

I attempted to use this expression and the results were not filtered by Instructor.

The problem I think I am running into is the data coming from two different tables and I am creating a summary table. 

So the data in power bi is setup something like this:

Table1

Instructor  RespID

Person1	1
Person1	2
Person1	3
Person1	4
Person1	5
Person1	6
Person1	7
Person2	1
Person2	2
Person2	3
Person2	4
Person2	5
Person2	6
Person2	7
Person3	1
Person3	2
Person3	3
Person3	4
Person3	5
Person3	6
Person3	7

Table 2

RespID Q1   Q2    Q3

1	5	4	2
2	5	5	5
3	3	4	4
4	1	1	3
5	5	5	5
6	5	3	1
7	5	4	2
1	5	5	5
2	3	5	4
3	4	2	3
4	5	4	5
5	5	3	1
6	1	4	3
7	4	5	5
1	2	4	4
2	3	3	3
3	5	5	5
4	4	3	1
5	4	4	3
6	5	5	2
7	3	3	3

Then, in Power Bi I use SUMMARIZECOLUMNS to bring the tables into a calculated table. From there I need the averages split by name. The result I get from using the ALLEXCEPT function is not filtered and returns one value for all rows. 

Is there a way to acheive this in a summarized table?

Thanks

[v-cgao-msft]

Hi @wcd1213 ,

Now there is a problem, according to the sample data, table 1 and table 2 do not have a primary key, which leads to the formula does not know how to summarize the data.

As an example, it is not clear which Person in Table 1 corresponds to the first record of Table 2.

Is there a primary key in real data?

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data