Forum Discussion
Weighted Average with Dynamic Weights
Hello,
I am performing a yearly survey amongst employees and am trying to automate the consolidation of the results. Here is some key information:
- The survey is divided into multiple sections, each containing one or more questions.
- Some questions have a pre-defined weight, others do not.
- Questions are not mandatory, so some answers might be blank.
My tables look as follows (simplified version):
Table 1 (Questions)
| Section | Question | Weight |
| T1 | T1.1 | |
| T1 | T1.2 | |
| T2 | T2.1 | |
| T2 | T2.2 | |
| T2 | T2.3 | 50% |
Table 2 (Results)
| Question | Respondent | Score |
| T1.1 | A | 4 |
| T1.1 | B | 3 |
| T1.2 | A | 3 |
| T1.2 | B | 2 |
| T2.1 | A | 1 |
| T2.1 | B | 2 |
| T2.2 | A | |
| T2.2 | B | |
| T2.3 | A | 2 |
| T2.3 | B | 1 |
What I am trying to achieve is the following:
| Section | Question | Weight | Average Score |
| T1 | T1.1 | 50% [1 / 2 questions] | 3.5 |
| T1 | T1.2 | 50% [1 / 2 questions] | 2.5 |
| T2 | T2.1 | 50% [1 - 0.5 (pre-defined weight) / 1 question (since T2.2 is empty) | 1.5 |
| T2 | T2.2 | ||
| T2 | T2.3 | 50% [pre-defined weight] | 1.5 |
In other words, I would like to create a measure that assigns a weight to each question based on the number of questions per section, ignoring questions that have no answer and taking into consideration pre-defined weights.
Since the user should be able to filter results by fiscal year or business unit, a calculated column is not an option (everything needs to be dynamic).
Then, I would like to calculate the weighted average per section (but I am assuming this is the most straightforward part of the exercise...).
I have been pulling my hair out for days trying to find a solution, so I am very much looking forward to your help!!
Thank you 😁
2 Replies
Hi inesj
Here is my testing.
First, merge the two tables.
Create two measures as follow
Weight1 = VAR _qcount = CALCULATE(DISTINCTCOUNT([Question]), FILTER(ALLEXCEPT(Merge1, Merge1[Section]), [Weight] = BLANK())) VAR _rcount = IF(MAX([Results.Score]) <> BLANK(), CALCULATE(DISTINCTCOUNT([Question]), ALLEXCEPT('Merge1', Merge1[Question])), BLANK()) RETURN DIVIDE(_rcount, _qcount)Average Score = CALCULATE(AVERAGE(Merge1[Results.Score]), ALLEXCEPT(Merge1, Merge1[Question]))Result:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Anonymous,
Thank you so much for your answer!
This is going into the right direction, but it is still missing the consideration of the pre-defined weight and seems to still count the questions with no answer:D3.3 in the above example has a pre-defined weight that is not being considered, and D2.1 should be 100% since D2.2 is empty (and hence not appearing).
Is there any way to incorporate this into the measure? I would assume this would require the following:
- New measure for the sum of pre-defined weights per section
- Substract this new measure from _rcount before dividing by _qcount
I have tried this with no success...
Many thanks in advance for your help!
Inès
