Calculate sum of weights for distinct ResponseIDs

[Salman_usman]

Hi,

I have a calculated data table (combined questions table). It has the following columns: Response ID, Question Number, Response Option, Question Option, Country Weight.

I am trying to calculate 'the sum of Country Weight for distinct Response IDs per question number'. Now this should be a measure. For example, say for Question Number 5, a total of 20 respondents have responded. This means there are a total of 20 distinct response IDs (the table might show the count as much more due to unpivoting but the distinct count is 20). For these 20 distinct response IDs, I would like to see the sum of Country Weights.

Now there are a couple of other tables in the data model: such as a demographics table that has country, age, income, etc. data. The calculation should be filterable by all these using slicers (the combined questions table is connected with the demographics table and things work fine in that area). Wanted to mention this so that we can take this into account while building the calculation.

Thank you in advance for your help.

Rough sketch of what I am trying to achieve is below:

[v-yiruan-msft]

Hi @Salman_usman ,

You can create a measure as below to get it:

Sum of distinct = 
VAR _tab =
    SUMMARIZECOLUMNS (
        'Questionnaire'[Question No.],
        'Questionnaire'[Response ID],
        "DistinctCW", MAX ( 'Questionnaire'[Country Weight] )
    )
RETURN
    SUMX ( _tab, [DistinctCW] )

Best Regards

Rena

[Salman_usman]

Hi @v-yiruan-msft , I received the below error message after adding your formula:

MdxScript(Model) (50,1) Calculation error in measure 'Combined Questions'[Measure]: SummarizeColumns() and AddMissingItems() may not be used in this context.

Not sure what's wrong 😞

[v-yiruan-msft · Accepted solution]

Hi @Salman_usman ，

Then you can create a calculated table with below formula:

Table 2 = 
SUMMARIZECOLUMNS ( 'Questionnaire'[Question No.],
'Questionnaire'[Response ID],
"DistinctCW",MAX('Questionnaire'[Country Weight]) )

Best Regards

Rena

[Salman_usman]

Perfect, this did the trick. One more thing, I need to have two columns in this table instead of 1. So both Country Weight and Global Weight. How do I tweak the formula for this please?

[amitchandak]

@Salman_usman , Try like

sumx(values(Table[Country Weight]),[Country Weight])

[Salman_usman]

@amitchandak Sorry some weird numbers coming 😞

We need distinct responseids and then a sum of the weights. Potentially, also include a filter in the calculation to point it to a particular question number and response type.