Forum Discussion

Frequent Visitor

2 years ago

Solved

Multiply average by value in related table

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 ...

Need Help

Anonymous
2 years ago
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.
Multiply average by value in related table.pbix33 KB

AnalyticsWizard

Solution Supplier

2 years ago

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 👍