Multiply average by value in related table

[inesj]

Hello!

I have a survey divided in different sections, each containing multiple questions. The average score for a section is based on the average score per question, multiplied by the question's weight. Here is what my tables look like:

Table 1 is related to Table 2 by the Section column, and Table 2 is related to Table 3 by the Question # column.

The desired output would be as follows:

Note that this should be done through measures rather than calculated columns, as I want the user to be able to apply some filters (e.g., filter out some respondents). 

This does not look like such a complex request, but for some reason nothing I have tried so far seems to be working...

I hope you can help!

Thank you. 

[v-tangjie-msft · Accepted solution]

Hi @inesj ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures. 

Average = CALCULATE(AVERAGE('Table3'[Score]),FILTER(ALLSELECTED('Table3'),[Question #]=MAX('Table2'[Question #])))

Avg = [Average] * MAX('Table2'[Weight]) 

Avg2 = 

SUMX(SUMMARIZE(ALLSELECTED('Table2'),[Section],[Question #],"a",[Avg]),[a])

Weighted Avg = CALCULATE([Avg2],FILTER('Table1',MAX('Table1'[Section]) in VALUES('Table1'[Section]) && MAX('Table2'[Question #]) in VALUES('Table2'[Question #])))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[AnalyticsWizard]

@inesj 

1. Ensure you have relationships set between your tables: Table 1 and Table 2 on 'Section', and Table 2 and Table 3 on 'Question #'.

2. Create a measure to calculate the weighted score for each question. This measure will multiply the score of each question by its weight.

Weighted Score = SUMX(Table3, Table3[Score] * RELATED(Table2[Weight]))

3. Create another measure to calculate the total weighted score per section. This measure will sum the weighted scores for each section.

Total Weighted Score by Section = CALCULATE([Weighted Score], ALL(Table3[Question #]))

4. Now, create a measure to calculate the sum of weights per section, which is needed to find the average.

Total Weight Per Section = CALCULATE(SUM(Table2[Weight]), ALL(Table3[Question #]))

5. Finally, create the measure to calculate the weighted average score per section. This measure will divide the total weighted score by the sum of weights per section.

Weighted Average Score = [Total Weighted Score by Section] / [Total Weight Per Section]

Make sure to replace the table and column names with the actual names used in your model.

When you use these measures in your report, you can apply filters, and the calculations will adjust accordingly because of the use of `ALL` which removes the filter context from the specified columns, allowing you to calculate the total regardless of any filters applied.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[inesj]

Hello @AnalyticsWizard,

Thank you so much for the quick reply!

Based on your advice, I have created the following measures:

Unfortunately, the results I am seeing are not as expected:

The value for D1.1 should be 2.00 ((3+1)/2), and the total for "D1 - Delivery Management" should be 3.00 (2.00*0.5 + 4*0.5). 

Is there anything I have overlooked?

Thank you! 🙂