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;
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! 🙂
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
Hi Dale,
Sorry for the late reply, been away for a few days. Anyways, here we go 🙂
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.
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
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
User | Count |
---|---|
372 | |
150 | |
84 | |
76 | |
73 |
User | Count |
---|---|
489 | |
216 | |
156 | |
124 | |
100 |