Weight in survey data

Question

I have a large amount of data from two surveys and i'm struggling with calculating certain percentages. I've tried to simplify it a lot here, but it does require a long setup.

In the data every respondent has been given an individual weight, so that for instant respondents in an age group with few respondents might count for a bit more than those in other age groups. Therefor in a given answer, it is not number of respondents but the sum of their weight that's the relevant.
The table for respondents look something like this, but with many more columns.:

'Respondents'

ID	Age	Weight
1	55	0,7
2	17	1,2
3	21	1,1

For quite a few questions the respondents could answer by choosing several options for instance "Which of the following options are you familiar with: A, B, C...". This give a tabel like:

ID	Uses A	Uses B	Uses C
1	0	1	1
2	0	0	0
3	1	0	1

The only way I can count those replies is by copying the table, remove unnecessary columns and unpivot columns A, B, C, so I get the following table which is related to the respondent tabel by ID:

'Q1_Options' tabel

ID	Uses	Weight
1	B	0,9
1	C	0,9
3	A	1,3
3	C	1,3

However, not all respondents have been asked all questions. So when calculating how many percentage answered yes to an option, the sum of weight of those answers should be held op against the collected weight of only those respondents, that have been asked the question.

In the example here the weight of for instance option A is 1,3.
The collective weight of the respondents who have been askes the question (ID 1 and 3) is 1,8
1,3 / 1,8 = 72%

But how do I write a dax query that can calculate the collective sum of only those who replied and where their weight is only counted once, but also is resistant to filters and rows so if can be used in the calculation of percentage?

My best guess so far is this, which doesn't work:

CALCULATE(          SUM('Respondents'[weight],          TREATAS(              VALUES('Q1_Options'[ID]),              Respondents[ID]          )         ,ALL('Options-table') )

It does return the right total, but when I put it in a matix it just calculates the weight of the answers, and therefor the percentage just returns 100% on all. I've also tried with REMOVEFILTERS with no difference.

shai_karmani · Accepted Answer

The issue is that VALUES('Q1_Options'[ID]) is being narrowed by the current Uses filter, so when the matrix row filters to Uses = A, only respondents who answered A end up in your denominator. You need to remove just the Uses filter for the denominator while keeping the question scope.
Try building it as two parts and combining them:
Weighted % =
VAR Numerator =
    CALCULATE(
        SUM('Respondents'[Weight]),
        TREATAS(VALUES('Q1_Options'[ID]), 'Respondents'[ID])
    )
VAR Denominator =
    CALCULATE(
        SUM('Respondents'[Weight]),
        TREATAS(
            CALCULATETABLE(VALUES('Q1_Options'[ID]), ALL('Q1_Options'[Uses])),
            'Respondents'[ID]
        )
    )
RETURN DIVIDE(Numerator, Denominator)
The numerator sums respondent weights for whoever chose the current option. The denominator uses ALL on the Uses column only, so the ID list expands back to everyone who was asked the question, and each respondent's weight is counted exactly once because the SUM is on the Respondents table rather than on Q1_Options.
If you have other option columns unpivoted into 'Q1_Options', extend the ALL to cover them, or use ALL('Q1_Options') if nothing else on that table needs to stay filtered.
If this helped, a thumbs up and marking it as the accepted solution would be appreciated.
Thanks,Shai Karmani
Let's connect in LinkedIn

Forum Discussion

Weight in survey data

2 Replies

Related content

Survey data from Microsoft form

Importing Survey Monkey data

Weighted Survey Total

Unpivoting data from Survey Monkey

Weighted data