Calculate Complex Average calculation with weight from four tables

[Anonymous]

     

 

Actually understanding the requirement is itself difficult just by reading.

I want to calculate average of scores each branch with weight from subject table for all the titles in Result table like below

Average for each branch =Sum of all (sum of scores for each weight * weight) for each branch/sum of weights for all the titles of that branch

For example for branch id 2 it's,

Sum of scores for weight 4 ( subject id 3) is 10 and sum of scores for weight 5 (subject id 4 and 5) is (2+1+2+3)=8

So there are 5 titles in result table for branch id 2

Average score for branch 2 = {(10*4)+(8*5)} / {(4)+(5*4)} = 3.33

I know it's little difficult to understand 

[Anonymous · Accepted solution]

Create two columns Related to TitleScore and Weight in Result table

Weight = Related (Subject [Weight])

TitleScore = Related (Score [score])

Create another column weight * Titlescore as below

Weight * Titlescore = Result [ Weight] * Result [TitleScore]

Create measure as shown below

average scores =Sum(Result[Weight*Titlescore])/Sum(Result[weight])

Use this measure in table with branchname to get average scores of each branch

[AntrikshSharma]

"Sum of scores for weight 4 ( subject id 3) is 10" shouldn't the score be 20?

You can refer to the PBI file at https://drive.google.com/file/d/1Pqa7WJ3Hnfobl9f5BvQfS9cIb5ph7SwH/view?usp=sharing

[amitchandak]

@Anonymous , Can share the same logic along with data in an excel workbook

[Anonymous]

Sure @amitchandak