I have the following dataset whereby there is an audit linked to the audit ID, each audit type has a number of questions - in the example Best has 14 questions and Tech has 12 questions. Audit 1 has an overall score of 73%, audit 2 has an overall score of 88%
| Review ID | Doc Review Case Answer ID | Review Type | Review Date | Reviewee | Review Score % Measure |
|---|---|---|---|---|---|
| 12345 | 676351 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676352 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676353 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676354 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676355 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676356 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676357 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676358 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676359 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676360 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676361 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676362 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676363 | Best | 30/06/2022 | Andy | 73 |
| 12345 | 676364 | Best | 30/06/2022 | Andy | 73 |
| 98765 | 676365 | Tech | 30/06/2022 | Andy | 88 |
| 98765 | 676366 | Tech | 30/06/2022 | Andy | 88 |
| 98765 | 676367 | Tech | 30/06/2022 | Andy | 88 |
| 98765 | 676368 | Tech | 30/06/2022 | Andy | 88 |
| 98765 | 676369 | Tech | 30/06/2022 | Andy | 88 |
| 98765 | 676370 | Tech | 30/06/2022 | Andy | 88 |
| 98765 | 676371 | Tech | 30/06/2022 | Andy | 88 |
| 98765 | 676372 | Tech | 30/06/2022 | Andy | 88 |
| 98765 | 676373 | Tech | 30/06/2022 | Andy | 88 |
| 98765 | 676374 | Tech | 30/06/2022 | Andy | 88 |
| 98765 | 676375 | Tech | 30/06/2022 | Andy | 88 |
| 98765 | 676376 | Tech | 30/06/2022 | Andy | 88 |
I wish to return the following
| Jun-22 | |
|---|---|
| Count of Reviews | 2 |
| Average Review Score % | 80.50% |
Which equates to 2 reviews totaling (73%+88%) 161% divided by 2 = 80.5%
However, because the overall percentage is applied to each question and across the 2 audits there are a total of 26 questions. I am getting a total percentage of (73%*14 questions+88%*12 questions) 2078%. If it was always the same number of questions it would be fairly easy, however because they can vary, it is becoming problematic.
Is there any way to account for this or get round this in Webi?