Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
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.
I don't know what are these numbers? (5+3)/2 = 4
But, as a description you can try like If(Match([Questions Text], '01'), 0, <Your expression here>)
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.