The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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
"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
@Anonymous , Can share the same logic along with data in an excel workbook
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
94 | |
85 | |
69 | |
65 |
User | Count |
---|---|
244 | |
128 | |
119 | |
82 | |
78 |