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

[tonijj]

Hi, 

I am in the middle of setting up an evaluation report in PowerBI, with many different Dimensions,

that are weighted.

Problem: I need to get the scoring, that will be very small, “translated” back to a scale of 1-5, where I will display the Average.

For some reason I cant upload picture so will post it below as a table or something,

Weighted DImension 1 = Goal

-Goal 1 = 20%

-Goal 2 = 30%

-Goal 3 = 50%

Weighted Dimension 2 = Levels

-Level 1 = 20%

-Level 2 = 30%

-Level 3 = 50%

Weighted Questions 

-Question 1 = 20%

-Question 2 = 30%

-Question 3 = 50%

The Question Weight aims to say that different questions are more/less important than other questions. For example; 

Question 1 - How important is it that the supplier offer a good Qlikview solution? 

Question 2 - How important is it that the supplier offer a good PowerBI solution to you ?

Obviously, Question 2 is more important, hence the higher percentage. 

Example:

-Question A has the following dimensions attached;

--Goal 2 (30%)

--Level 3 (50%)

+ Question Weight 20%

The evaluator sets a score of "5" on Question A. That gives us a weighted score of: 5 x 0,30 x 0,50 x 0,20 = 0,15pts.

So, lets say that there are 100 questions, all related to these different dimensions, all questions are connected to a dimension 1, 2 etc, and they have a relationship table setup.

What i do:

• Summarize all the questions, where I can analyze per dimension(s). - That calculation is all done in PowerBI and works

So, back to the issue at hand - I might get a total combined score of 0,95 for Question A when all 10 evaluators has set their score. 

Problem 1

First of all - I want it to show a result of 1-5pts because that is the scale that the people are rating by, and having i.e a score of 0.95 wouldnt make any sense when presenting the result.

Problem 2

I need the solution above (Problem 1) to be the Average. Simply because one Dimension 1 might have 99 questions, and Dimension 2 might only have 1 question, hence the result would be very misguiding. 

Im thinking that If Problem 1 is solved, it would be easy to just display it as an Average directly in PowerBI. But, I just felt I neede to mention it. 

Truly would appreciate help here since I just cant get my head around Problem 1. I actually got the other calculations and summaries to work (thank you Calculate and SumX!!), but yeah... Im stuck.

Thanks for everyone trying to help! 

[v-jiascu-msft]

Hi @tonijj,

Could you please a sample with dummy data? The solution could vary from different source data.

About question 1, you have showed up the solution: 5 x 0,30 x 0,50 x 0,20 = 0,15pts. Isn't it?

Best Regards!

Dale

[tonijj]

Hi Dale, 

Sure thing! However, I can only upload photos/videos in the forum so I uploaded the two sample files onto my NAS.

https://johansson.myqnapcloud.com:443/share.cgi?ssid=02tZf11

There are two files;

1. Excel file with the scoring data, and all the dimensions

2. The BI file where I setup the relationships, and made some summary measurements. 

The measurement that is the total of the weighted score is called "ScoreTotal". THose numbers are the ones I want to be able to show as average and as a 1-5 scale. 

[v-jiascu-msft]

Hi @tonijj,

As I can see, you almost have done all the work. The result of "ScoreTotal" is 1 to 5.  I have a few questions.

1. You have already calculated the ratio for every single question, right? "FinalWeightBeforeGroup"?

2. Which numbers do you want to show as average?

Best Regards!

Dale

[tonijj]

Hi Dale!

Yeah, most of the work is done, its "just" the work to normalize the field "ScoreTotal" to a scale of 1-5 again. But, that field does not show a result of 1-5 in the current model.

1. Yes - I did it as calculated columns in Excel. Its under the Data tab in the excel file, correct. I did it as a simple step by step, mainly because I had issues getting the DAX formula correct showing the correct totals for instance. So, I made a quickfix in excel, but hey, it works 🙂

2. I want the numbers 3,48 and 3,65 as Average. I assume you have made those as a Sum correct? Since those numbers are built up by many small numbers of scoring (e.g. 0.17 + 0.01 etc) it doesnt show the average now.

Secondly, If I slice it down per the dimension "Goal" for instance, there might only be 10 questions, and then it wont be a number of 3.48, but maybe something like 0.97 in total.

Therefore, if we Normalize the 0.97 to a scale of 1-5 instead, it would make so much more sense when presenting it. The trick (I guess) is that it would have to be normalized per whatever dimension I filter on at that particular moment. 

For instance, if I filter by "Goal" dimension, and on one specific Goal (e.g "Quality") I would get a total sum of 0.97 or something, then its the 0.97 number that needs to be normalized to a scale of 1-5.

If I however do the filter as per your picture, on Supplier A, then the sum of 3.48 for instance needs to be normalized to 1-5 scale.

I was thinking one could do a new Measure, taking the "ScoreTotal" and divide it by "5". That would (I think) give a scale that could be translated back somehow to 1-5 in a next step? But I dont know... again, Im stuck 🙂 

[tonijj]

Dale,

Just wanted to reach out and see whether my explanation was answering your questions or not? Let me know

Cheers

Toni

[v-jiascu-msft]

Hi @tonijj,

I almost get the whole idea of the mode. To be honest, I am struggling with the weighted score: 5 x 0,30 x 0,50 x 0,20 = 0,15pts. Why do they multiple each other? In order to show them in 1 - 5 pts scale, we need to evaluate the full score they can get. 

1. For example, there are 3 questions with 5 score each. So the total score is 15. If one get the total score 9, it would be 3 (9/15*5) of scale 5 or 60 (9/15*100) of scale 100. So the point is what the total score is.

2. If you want the average value, maybe you could try this formula.

ScoreAverage =
AVERAGEX ( RFP_Score, RFP_Score[Score] * RFP_Score[WeightToUse] )

3. I tried to evaluate the full scores, but it looks strange.

FullScore =
SUMX (
    'RFP_Score',
    'RFP_Score'[Score]
        * LOOKUPVALUE (
            'MainQuestionTable'[QuestionWeight],
            MainQuestionTable[QuestionKey], 'RFP_Score'[QuestionKey]
        )
)

Best Regards!

Dale

[tonijj]

Hi Dale, 

Ok lets see if I can explain it a bit better regarding the multiplication. With that being said, my thinking might be off 🙂 

The multiplication comes from the fact that I weight different parameters differently within a dimension. Lets take a live example; 

Dimension = "Levels". 

The different parameters within the Dimension "Level" are; Strategic, Tactical and Operational.

So, I classify ALL questions to this dimension stating that 1 question is either a Strategic, Tactical or an Operational question. 

BUT, and here's the kicker... I say (for example) that Strategic questions are more important that Operational questions. Hence, I put a weight to the different parameters, within the same Dimension (Level). So it could look like this; 

Strategic = 35% (importance you could say...) 

Tactical = 40%

Operational = 25%

So, if the question is an Operational question, I multiply it by 0,25% to add the Weighting (importance) of it. 

Then I do the same for the other dimensions that carry a weight. 

Does it make more sense now?

Again, if there is a better way than "just" multiplying Im all ears!!!

Cheers

Toni

Ps. Thanks for the patience, u sure deserve a beer (or two) by now 🙂

[v-jiascu-msft]

@tonijj

Hi Toni,

I think I worked it out. Maybe. Your idea is totally correct. The problem could be the way to achieve it. Let's start from the basic example. If there is only one dimension, let's say Goal (goal 1 and goal 2), it would be like this. The total scores is 5.

Goal      Weight

Goal 1    0.2

Goal 2    0.8

1. If there is only 1 question, it isn't necessary to weight it by Goal. 

2.1 If there are two questions, and if they are weighted by goal 1 and 2 respectively, there result is 5 * 0.2 + 5 * 0.8 = 5. (in this scenario, goal weight is also importance weight.)

2.2 If there are two questions for Goal 2, it would be (5 * 0.8 + 5 * 0.8) / (5 + 5) * 8 = 4. Or evaluate by importance weight, which is 0.5 I gave them. 5 * 0.8 * 0.5 + 5 * 0.8 * 0.5 = 4. 

2.3 If there are more than two questions, firstly, we should get importance weight, secondly, evaluate each score * goal weight * importance weight, finally sum them up.

Because there are more than one question for 1 goal, importance weight (or proportion) is needed.

If there are two factors, such as goal and objective, there is a Cartesian product actually.

Goal    Weight       Objective      Weight

goal 1  0.2              O1                 0.3

goal 2  0.8              O2                 0.7

The whole proportion must be 1. So where is it? If we do some simply times 0.2 * 0.3, 0.8 * 0.7 and sum them up, we would miss some parts. Here is the right way.

Num   Goal      Objective   Weight

1         goal 1    O1             0.06

2         goal 1    O2             0.14

3         goal 2    O1             0.24

4         goal 2    O2             0.56

total                                    1

There are two weights we need to consider now. Importance weight and dimension weight. If we don't use importance weight, we could use (total scores / grand total scores). Using importance weight would be easier. 

The details are in the file. Please check it out. And here is some explanations:

1. There is a mistake in your source data. 4 in the questionweight.

2. Power BI is very powerful. I would do the calculation in the Power BI other than importing from a workbook.

3. The result is in the table "0_Result_table". I prefer a table as result to a measure.

4. If you have more dimensions, you can do it like this.

5. The result is: (BTW, I did a verification of one person and one supplier in the excel)

and the file is: https://1drv.ms/f/s!ArTqPk2pu-BkgQmAcrK3WckgPR1o

Best Regards!

Dale

[tonijj]

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! 🙂 

[v-jiascu-msft]

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

[tonijj]

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. 

[v-jiascu-msft]

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

[tonijj]

No one?? 😞