Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Evaluation - Weighted dimensions - Back to 1-5pts ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

tonijj

Helper III

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-08-2017
08:14 AM

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!

13 REPLIES 13

Highlighted
##

tonijj

Helper III

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2017
06:38 AM

No one?? 😞

Highlighted
##

v-jiascu-msft

Microsoft

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2017
06:46 AM

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

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.

If this post

Highlighted
##

tonijj

Helper III

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-09-2017
07:13 AM

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.

Highlighted
##

v-jiascu-msft

Microsoft

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-10-2017
02:42 AM

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

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.

If this post

Highlighted
##

tonijj

Helper III

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-11-2017
12:41 AM

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 🙂

Highlighted
##

tonijj

Helper III

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-14-2017
11:38 PM

Dale,

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

Cheers

Toni

Highlighted
##

v-jiascu-msft

Microsoft

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-15-2017
01:04 AM

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

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.

If this post

Highlighted
##

tonijj

Helper III

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-16-2017
11:18 AM

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 🙂

Highlighted
##

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.

v-jiascu-msft

Microsoft

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-20-2017
07:34 PM

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

If this post

Announcements

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.

Top Kudoed Authors

User | Count |
---|---|

472 | |

211 | |

149 | |

130 | |

97 |