0

I'm trying to formulate weighted average measures in PowerBi for survey data. I've currently got the following two tables (simplified):

Survey Data

ID           How would you score the service?      Do you agree with X?
---------------------------------------------------------------------------
23           Fair                                  Agree
24           Poor                                  Strongly disagree
25           Fair                                  Agree
26           Very poor                             *blank*
27           Very good                             Strongly agree

Weights

Weight         Score         Likert
-------------------------------------------------
 1             Very poor     Strongly disagree
 2             Poor          Disagree
 3             Fair          Neither agree nor disagree
 4             Good          Agree
 5             Very good     Strongly Agree

There's currently a relationship between 'surveydata'[How would you score the service?] and 'weights'[weight].

The weighted average formula I'm trying to calculate is the following:

                (x1 * w1) + (x2 * w2) + ... (xn * wn)
Weighted Ave = ___________________________________________
                          x1 + x2 + ... xn

Where:

w = weight of answer choice
x = response count for answer choice

For the example above, I would need two measures - a weighted average for 'surveydata'[How would you score the service?] and one for 'surveydata'[Do you agree with X?].

For the example above, the weighted average measure for 'surveydata'[How would you score the service?] should be 2.8.

Note that one of the complications is the fact that there are *blank* cells.

Can anyone suggest a way of doing this in PowerBI with calculated measures (or otherwise)?

Alex Catalán Flores
  • 1
  • 1
  • 2

1 Answers1

0

You can sum the weights for each row in your SurveyData table and divide by the number rows

Weighted Ave =
DIVIDE(
    SUMX(SurveyData, RELATED(Weights[Weight])),
    COUNTROWS(SurveyData)
)

Or simply use an average function

Weighted Ave = AVERAGEX(SurveyData, RELATED(Weights[Weight]))
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Hi Alexis! Thank you for your response, it was very helpful. I realised, however, that my example was too simple for the problem I've got. I'm sorry about that. I've now changed it to better reflect my data sets. My problem now is that your measure works perfectly, but how do I make it work for another column that also has related information? I've added the `'surveydata'[Do you agree with X?]` and the `'weights'[Likert]` columns to better reflect my issue. Thank you again, Alexis! It's super helpful. – Alex Catalán Flores Oct 14 '18 at 23:26
  • You would need to have the relationship on the other column. Check out the `USERELATIONSHIP` or `LOOKUPVALUE` functions. See here for example: https://www.sqlbi.com/articles/userelationship-in-calculated-columns/ – Alexis Olson Oct 15 '18 at 15:15