DAX suggestions to generate specific weighted average measure in PowerBI?

Question

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)?

Alexis Olson Alexis Olson · Accepted Answer · 2018-10-12T16:24:40

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]))

DAX suggestions to generate specific weighted average measure in PowerBI?

1 Answers