Calculate sum of weighted average to use in Gauge visual

[eriwet]

Hi, I have a large list of quotes where most quotes are classified with a summary statement in a separate column (several quotes can have the same summary) and each quote is then classified in terms of how well it aligns to the summary.

Each "quote summary alignment" is given a value (-2 if it contradicts the summary, +2 if it corresponds well etc)

Now I wish to create a gauge visual that can display the "total score" in relation to the possible max & min values (non-classified quotes  should be ignored).

Any ideas on how to achieve this?

[eriwet]

Hi and thanks for trying to help here!

The idea was that the -2, -1, 0, 1, 2 are the weights. (the quotes we have in the original Excel have been classified with a label (strong agreement=2, some agreement=1, neutral=0, some disagrement=-1, strong disagreement=-2).

So basically what I want to do is to mulitply the quote count value with the alignment value for each label and then sum it all up and then present this total score with a gauge having the max value of the gauge correspond to having all quotes labeled as "strong agreement" and min value correspond to all quotes labeled as "strong disagreement".

Hope that makes some sense...

[lbendlin]

This all sounds very nice until you try to multiply 49 by 0 .  Are you willing to ignore these quotes and only focus on the over/under?

It might be better to shift your scale away from 0 (for example from 1 to 5 ) for a simpler calculation.

[eriwet]

Ok, I get that division by zero might be tricky, but multiplication?

but ok if that makes things easier, then I guess 1-5 might work just fine.

[lbendlin]

In that case the weighted average would be 4.28, and shifted bak to your original scale it would be 1.28 .  Now what ? Display on a gauge?

[eriwet]

Well something like that - I realize "weighted average" is maybe the wrong term to use here. It's more like a "weighted sum" I'm trying to achieve.

If we look at this exemple

the total score should be 8x(-2) + 15x(-1) + 5x1 + 18x2 = 10 

That means that for this particular Quote Summary people tend to agree a little bit more than they disagree.

For the gauge the the min value should be 46x(-2) and the max of course should be 46x2

If there had been a few "neutrals" they would have added 0 points (and that is exactly what I want) But they should contribute to the min/max value of the gauge.

The reason I wish to have minus values and zeros is because it is more intuitive for me (and the users) if quotes that opposes the Summary doesn't yield positive points the total score.

My problem is that i cannot seem to find a way to first mulitply the quote count and then add it all up.  I'm sure it is trivial though - but as a PBI beginner I've not been very sucessful in figuring it out  

[lbendlin]

I don't think you can ignore the ones in the middle.  Let's say you have

-2	8
-1	15
0	5
1	5
2	18

as one sample and

-2	8
-1	15
0	1200
1	5
2	18

as another sample.  Surely the results should be treated very differently?

[eriwet]

Yes the result will be different and that is exactly what I want if I have 1200 people expressing an opinion about a topic but clearly don't side with either for or aginst the quote summary - that should affect the result - but only in ways that reduce the relative importance of those who agree or disagree.

So that is definitely the way it should work. That is why giving "neutral" a value of 0 is better than assigning it the value of 3.

But again, my question is not about which weights I should or should not use - it is about PBI and how to multiply the count of one parameter with a weight and then summarize the results.

Probably really really simple.

[lbendlin]

"

that should affect the result - but only in ways that reduce the relative importance of those who agree or disagree.
So that is definitely the way it should work. That is why giving "neutral" a value of 0 is better than assigning it the value of 3."

No, exactly the opposite.  I think my approach (temporarily shifting, calculating, then shifting back) gives you a more appropriate weighted response.

[eriwet]

That might be so, but again that is not what I'm asking

[eriwet]

Hi again, just noticed your code above in the included image (somehow managed to miss that) 🙄 That is what I'm looking for and it looks pretty much exactly how I would go about doing it. BUT in my case it seems I cannot access my weigth values. I have the "values" in a lookup table and the count of occurances of each label is done in my data table.

No matter how I write it DAX refuses to accept any reference to my weight values 

[V-lianl-msft]

Hi @eriwet ,

We can not reference dynamic measure in static columns or tables.If you reference the value of a column in measure, please aggregate this column. 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[eriwet]

Hi Liang and thanks for your input. However, I don't quite understand what you mean. I get that for some reason I cannot reference the lookup tables I've created regardless of the type of content in the columns or if it is ameasure or whatever - none of my lookup tables are visible when I write DAX to create an explicit measure. 

I've gotten the impression that splitting a huge flat table into facts and lookup tables is best prectice - but given this inability to use the lookup tables in measures makes me question this.

[lbendlin]

How do you want your weight scale (-2,-1,0,1,2) influence the quote count?  Is it a linear multiplier, some quadratic function or a logarithm? how much more weight does a -2 have than a 0 ?