Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I have a challenge, and there are some similar issues in the forum, but not quite the same one unfortunately.
What I try to achieve?
-A supplier evaluation based on 10 questions.
-The evaluation is steered by 3 main Goals for the evaluation. Each goal is differently weighted. Hence, the different weighting should have an impact on the total result.
-Each question is assigned 1 Goal
-Each person rates each question on a scale to 1-4
The Result I would to achieve:
-Weighted scores (based on the importance of the different goals)
-Presented as a Score of 1-4 per supplier
Without the weighting its straight forward yes, but I just cant seem to crack that final thing with the weighting.
Would really appreciate any help!
Attaching sample data in Excel here.
Bump - any other takes to this?
To achieve a weighted supplier evaluation score, you'll want to calculate each question's score based on the goal it belongs to, apply the goal-specific weights, and then calculate a weighted average to get a total score for each supplier on a 1-4 scale.
Here's a step-by-step approach to structure the solution in Power BI:
Ensure your data model has these elements:
For each question, you'll calculate a weighted score that considers the goal's importance. Use a Calculated Column in Power BI to do this. Assuming you have a relationship between your Questions Table and Ratings Table based on Question ID, create the following calculated column in the Ratings Table:
This formula multiplies the Rating by the Weight of the corresponding goal for that question.
To calculate a total weighted score for each supplier, create a Measure in the Ratings Table:
This measure will sum up all the weighted scores for each supplier, giving you a total score that accounts for the weight of each goal.
Since you want the final score to be on a 1-4 scale, calculate a Normalized Score. Create another Measure for this, which divides the TotalWeightedScore by the sum of the weights to ensure it fits the 1-4 scale:
This NormalizedScore measure will provide the final weighted score for each supplier on a 1-4 scale, accounting for the goal weights.
Now, you can display the NormalizedScore measure in your report visualizations to show the weighted score per supplier, which should reflect the weighted impact of each goal.
This setup should give you a flexible and scalable model that allows each goal’s weight to influence the total supplier score effectively.
If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!
@PavanLalwani Any thoughts on my comments below regarding this? Feels that the solution is really close 🙂
Hi @PavanLalwani
First of all, I owe you a beer, what a comprehensive and understandable answer, thank you!
I think I seem to miss one piece of the puzzle, as some has a higher average than the 1-4 scale.
There are a few above 4, and some that are below 1, which shouldnt be possible. And Yes, I surely understand that its most likely me who done something wrong 🙂
See PBIX and reference Excel data here.
Link your goals table one-to-many to questions, and questions one-to-many to the data. Then your measure could be
Weighted Score =
SUMX ( 'Data', 'Date'[Score] * RELATED ( 'Goals'[Weight] ) )
Hi @johnt75
Thanks for the fast answer!
Howeve, that doesnt give me the Rating result on the Scale of 1-4 😞
It "simply" summarizes all scoring,
Not sure I understand how you hope to achieve a ranking of 1-4. How do you want to turn the weighted result into a ranking ? You could rank each supplier, either overall or by question, based on the weighted score, but there are only 3 suppliers in the sample data so that would only give you a ranking of 1-3.
Hi @johnt75
Apologies if I was unclear.
It shouldnt say "ranking" but rather "Scoring". Meaning, I want the weighted score to go back to a 1-4 scale as thats the scale the users have used to rate the suppliers.
Make sense now? again, sorry for the misunderstanding 🙂
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |