I am creating a pivot table with two dimensions (row = unique question, column = roles). The data is like below. I want to calculate an average score on questions 02 - 04 per question per role for all project IDs. The score is based on answers to questions and for Question 01 the score will always be 0 and should be always be excluded.
For example, the outcome for Role B for Question 02 would be (5+3)/2 = 4
Project ID
Questions Text
Answer score / Text
1
01
Role A (0)
1
02
5
1
03
4
1
01
Role B (0)
1
02
5
1
01
Role C (0)
1
03
4
2
01
None (0)
2
02
0
2
03
3
2
01
Role B (0)
2
02
3
2
04
4
Can anyone help with me on the function and expression to use assuming I cannot alter the table? Thanks in advance.
Thank you Anil for your response. Just to clarify I was making an example of the average number i was looking for given the role B for question 02.
I ended up using a different dimension for a similar visual which solved the issue I had. I think the real challenge is the Role is in the same column of the measures I need to calculate. Without extracting the Role as a separate column, it's very difficult.
Thank you for your input. I will keep in mind the match function for future usage.
But, as a description you can try like If(Match([Questions Text], '01'), 0, <Your expression here>)
Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Thank you Anil for your response. Just to clarify I was making an example of the average number i was looking for given the role B for question 02.
I ended up using a different dimension for a similar visual which solved the issue I had. I think the real challenge is the Role is in the same column of the measures I need to calculate. Without extracting the Role as a separate column, it's very difficult.
Thank you for your input. I will keep in mind the match function for future usage.
