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%
-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.
-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:
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.
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.
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!
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?
Sure thing! However, I can only upload photos/videos in the forum so I uploaded the two sample files onto my NAS.
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.
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?
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 🙂
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] ) )
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!!!
Ps. Thanks for the patience, u sure deserve a beer (or two) by now 🙂
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 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
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
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.
Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.