Weighting with Goals and Total Score

[tonijj]

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. 

[tonijj]

Bump - any other takes to this? 

[PavanLalwani]

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:

1. Set Up Your Data Structure

Ensure your data model has these elements:

• A Questions Table with columns for Question ID, Goal, and Weight. Each question should be assigned to a goal, and each goal should have a specific weight (e.g., Goal 1 = 30%, Goal 2 = 50%, Goal 3 = 20%).
• A Ratings Table with columns for Question ID, Supplier ID, and Rating (on a scale from 1 to 4).

  2. Calculate Weighted Scores for Each Question

  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:

  DAX

  Copy code

  WeightedScore = RELATED(Questions[Weight]) * Ratings[Rating]

  This formula multiplies the Rating by the Weight of the corresponding goal for that question.

  3. Calculate the Total Weighted Score for Each Supplier

  To calculate a total weighted score for each supplier, create a Measure in the Ratings Table:

  DAX

  Copy code

  TotalWeightedScore = SUMX( Ratings, Ratings[WeightedScore] )

  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.

  4. Normalize the Total Score to a 1-4 Scale

  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:

  DAX

  Copy code

  NormalizedScore = DIVIDE( [TotalWeightedScore], SUM(Questions[Weight]) )

  This NormalizedScore measure will provide the final weighted score for each supplier on a 1-4 scale, accounting for the goal weights.

  5. Display the Final Results

  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.

  Additional Tips

  • If you need to adjust the weightings, you can do so in the Questions Table without changing the underlying calculations.
  • Ensure that all your weights add up to 1 (or 100%) to keep the final score on a comparable scale.

    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!

[tonijj]

@PavanLalwani Any thoughts on my comments below regarding this? Feels that the solution is really close 🙂 

[tonijj]

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. 

[johnt75]

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] ) )

[tonijj]

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,

[johnt75]

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.

[tonijj]

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 🙂