cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Re: Evaluation - Weighted dimensions - Back to 1-5pts scale

Hi Dale, 

 

Wow, what an effort!! Hats off 🙂 

 

So, I've been studying your files for almost a day now, think we are getting closer to a final solution here! Here are some of my thoughts;

 

  1. Your comment regarding that I had an error in my source data - did you refer to the fact that I didnt include the dimension "Level" as part of weighting each individual question? I only used the dimension "Goal" as when weighting each question. It basically means that I only weight questions against each others within one specific Goal, 
  2. "The whole proportion must be 1." - I think I agree, I just dont understand where I went wrong to be honest 🙂 
    1. Question - If we convert it to a proportion of 1, couldnt we just make a translation table in percentage (scoring) and translate it back to a scale of 1-5. I added a quick Excel example in the link in my previous post. 
  3. Question - What you call "Importance Weight" is actually the weighting of each individual question right?
  4. I really like your idea of creating a new consolidated table instead of a measure. I will use that for sure! What I did, was to add "QuestionKey" to your table, and created a relationship between the "MainQuestionTable" and "0Resulttable" so I can analyze the result on a; Level/Goal and Question basis. 
  5. Average - With evaluation models I've always worked with using the Average score to get a score of 1-5. Guess thats out of the question once starting to weight different dimensions?
  6. Follow-up to #5 - When I add the dimensions to the view in PowerBI (as per your screenshot) the values are then broken down in to smaller fragments/sums. The calculation "Scores" that you created should always reflect a scoring range of 1-5. Do we solve that via #4 above or by something else? Will try myself as well, but im out on thin ice here 😞 

So, to close this item, what, in your opinion do I/we have to do? I feel that the solution is so close that I can almost taste it! 🙂 

 

powerbi 1-5.PNG

Highlighted
Microsoft
Microsoft

Re: Evaluation - Weighted dimensions - Back to 1-5pts scale

Hi @tonijj,

 

1. The value of "questionweight" of one row is 4, which is wrong.

2. Let's simplify your model. It would be a good way to find out the key point. 

1) Only two factors "Goal" and "Objective";

2) Only five questions. All importance score is 5. 

3) By your method, the importance weight only considers goals. So take question 1 for example, the total importance score is 5*3=15. The ratio 1 is 5 / (5 + 5 + 5) * 0.2 * 0.3=0.02. If we consider two factors, (g1, o1) would be a group. Then ratio 1 is 5/(5+5) * 0.2 * 0.3 = 0.03.

4) The total of "Ratio 1" is 0.4, which isn't right. The total of "Ratio 2" is 1.

5) The scores are 4, 4, 5, 4, 3. The final result 1 is 1.35, while the final result 2 is 3.58. It's obvious 3.58 would be more sensible.

6) You used a ratio in your model. Maybe there is no needs to translate it back to 1-5. The other way is using score directly, which needs translation. For instance, sum up all the scores 3 + 4 + 5 = 12. The full score is 15 (we assume), Then the translation would be (12 / 15) * 5 = 4. (back to 1-5 now, not 12).

Goal   Weight Objective Weight						
g1	0.2	o1	0.3						
g2	0.8	o2	0.7						
									
Goal   Obj  importance score	YourMethod	Ratio1	Result1	TwoFactor	Ratio2	Result2
g1	o1	5	4	5/15*0.2*0.3	0.02	0.08	5/10*0.2*0.3	0.03	0.12
g1	o1	5	4	5/15*0.2*0.3	0.02	0.08	5/10*0.2*0.3	0.03	0.12
g1	o2	5	5	5/15*0.2*0.7	0.05	0.23	1*0.2*0.7	0.14	0.7
g2	o1	5	4	5/10*0.2*0.3	0.03	0.12	1*0.8*0.3	0.24	0.96
g2	o2	5	3	5/10*0.8*0.7	0.28	0.84	1*0.8*0.7	0.56	1.68
				        	0.4	1.35		         1	3.58

3. I would say the "importance weight" is the contribution of each question. Without the importance weight, the score could be bigger than the full score 5.

4, 5, 6 I am a little confused. But one thing, we should consider the context in the final report. As we can see the first row of your screenshot, Jenny Doe Supplier B get score 0.39 in the condition Operational and Quality. NOT Jenny Doe Supplier B get score 0.39.

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper III
Helper III

Re: Evaluation - Weighted dimensions - Back to 1-5pts scale

Hi Dale, 

 

Sorry for the late reply, been away for a few days.  Anyways, here we go 🙂 

 

  1. Understood. will look and fix the error. 

 

  1. OK

 

  1. OK, understand that if I dont use both Goal and Objective, it will mess upp my ration getting to "1". Dont quite understand why, but lets use that as a working thesis that we have to go this way 🙂 

 

  1. Well yes,, I agree 3.58 is more sensitive, but is it truly correct? If its one thing Ive learned about PowerBI is that you could get really "nice" numbers, but they are not always correct 🙂

 

  1. I think it is important to scale it back to 1-5 for the purpose that the people actually evaluating, will score from that specific scale (1-5). It would be much more difficult to understand the relevance for them when presenting the end result.

 

You lost me at the calculation " 3 + 4 + 5 = 12. The full score is 15 (we assume), Then the translation would be (12 / 15) * 5 = 4. (back to 1-5 now, not 12)."

 

Since those scorings seem to be in different Dimensions, diff Goals and Objectives.  But in any case, what you did there is an Average Score, which I would need to display in the BI model , but is simple by just selecting "Average" under the Values in "Visualizations". 

 

 So to put it bluntly, these are the (high level) steps we are trying to achieve; 

 

A) Weight the question scores accoring to dimensions - OK Ratio2
B) Normalize the scoring in A) back to the scale of 1-5 - Not completed 

C) Present B) as an Average in a PowerBI model - Ok in the sense that it would be a simple thing once B) is solved. 

 

 

 

 

 

   

 

 

Highlighted
Microsoft
Microsoft

Re: Evaluation - Weighted dimensions - Back to 1-5pts scale

Hi @tonijj,

 

Let's leave this alone and discuss some questions below. They are all independent.

1. A questionnaire with three questions. No importance weight, no other weights. What is final score?

 

Question   Full Score	Actual Score
Q1	       5	   4
Q2	       5	   3
Q3	       5	   5

4 + 3 + 5 = 12? That isn't good for understanding. If we translate it into hundred-mark system, it would be (12/(5 + 5+ 5))*100= 80 points. If we translate it into five-mark system, it would be (12/(5+5+5))*5 = 4 points.

 

2. What is final score of this sample? My answer is 1. Though there are many questions of g1, its weight is 0.2. What is your answer? Maybe we can find out if we have same idea about this thread.

 

Goal   Weight       						
g1	0.2	     						
g2	0.8	  					
Goal	Full Score	Actual Score
g1	       5	5
g1	       5	5
g1	       5	5
g1	       5	5
g1	       5	5
g1	       5	5
g1	       5	5
g1	       5	5
g1	       5	5
g1	       5	5
g2	       5	0

3. 3.58 is the answer of mine, not of Power BI. If you have such a sample, what is your answer? To me, there is no need to translate back to 1-5, because I have applied proper ratio. 

 

4. If all the scores are 0 by accident, how to translate the final result to a score that bigger than 1. This is possible. Because the formulas don't care the source. It returns value or error.

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors