Weighting with Goals and Total Score

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!