Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
tonijj
Helper IV
Helper IV

Weighting with Goals and Total Score

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. 

 

 

8 REPLIES 8
tonijj
Helper IV
Helper IV

Bump - any other takes to this? 

PavanLalwani
Resolver II
Resolver II

 

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!

@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. 

Skärmbild 2024-10-30 135350.png

 

johnt75
Super User
Super User

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 🙂 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.