Average of Multiple Columns by a single selection

[Daniel_B]

Hi All

I have a table which contains 10 columns (each one is a question) and each question can have a value between 1 and 5 (inclusive of) and each row is a different location

I need to work out the average of all 10 questions by each location for a single overall average - below is a small example of the data

Location      Question 1  Question 2 Question 3  Question 4

Building A         4                   3               2                  1

Building B         3                   5               3                  3

Building A         2                  5                4                   3

Building A Average = 3

Building B Average = 3.5

There are other questions/buildings but as long as I understand this part I can adjust the rest for my data

Thanks

Dan_B

[v-alq-msft]

Hi, @Daniel_B 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[az38]

Hi @Daniel_B 

try a measure (replace 4 to 10 for your data)

Measure = CALCULATE(AVERAGEX(Table1;(Table1[Question 1]+Table1[Question 2] +Table1[Question 3]+Table1[Question 4])/4);allexcept(Table1;Table1[Location]))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Daniel_B]

hi @az38, I gave it a go with just 2 questions to see if the average was correct but it is not correct when I manually work it out against the source data

I also excluded building filters just to see if it works against total values and again it still shows incorrectly by about 0.5 

Any ideas what could be wrong?

I've tried to see if blanks/0's make any difference but I cannot see that as the issue

[az38]

@Daniel_B 

0.5 is may be round question. Set parameter Decimal places

if difficult to say something about correct/incorrect data without data example.

for your 3 buildings and 4 columns my solution works OK

do not hesitate to give a kudo to useful posts and mark solutions as solution