Forum Discussion

inesj's avatar
inesj
Frequent Visitor
2 years ago

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, ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    inesj
    Frequent Visitor
    2 years ago

    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