Weighted Average with Dynamic Weights

[inesj]

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 😁

[Anonymous]

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 Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[inesj]

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