Measure to add multiple columns inside summarize and divide these columns

[Anonymous]

Hello,

I am trying to write a measure to count how many respondents used only one answer in a survey. (If their most frequent answer is used more than in 90 per cent, then I label them as "used only one answer")
The survey is getting data every year.

I have this input data:
(questions are various)

Respondent	Year	Question	Answer
1	2022	…	Agree
1	2022	….	Disagree
1	2022	…	Don't know
1	2022	.	Agree
1	2022	.	Disagree
2	2022	…	Agree
2	2022	.	Disagree
2	2022	.	Don't know
2	2022	.	Agree
2	2022	.	Disagree
2	2022	.	Don't know

My solution recently:
Table with summarized rows for the same answer, respondent number, and year.
Then added a column to count occurrences for each answer by every respondent and a column for the number of all answers by the respondent.

Respondent	Year	Answer	Number of answer occurencies	number of all answers	Divide
1	2022	Agree	2	5	0,4
1	2022	Disagree	2	5	0,4
1	2022	Don't know	1	5	0,2
2	2022	Agree	2	6	0,333333
2	2022	Disagree	2	6	0,333333
2	2022	Don't know	2	6	0,333333

Then I have a measure, that filters the Divide column values above 0.9 and count rows.

I want to have only the measure using summarize etc because the output of this calculation is always only a number.

I tried to use function summarize, addcolumn and reletedtable, so I created the table displayed above with the number of answer occurrences. But I failed to get other "columns" using summarize, so I would be able to filter and count the rows only using input data and measure.

Thanks for your help 🙂

[v-zhangti]

Hi, @Anonymous 

You can try the following methods.

Measure:

Number of answer occurencies =
CALCULATE (
    COUNT ( 'Table'[Answer] ),
    FILTER (
        ALL ( 'Table' ),
        [Respondent] = SELECTEDVALUE ( 'Table'[Respondent] )
            && [Answer] = SELECTEDVALUE ( 'Table'[Answer] )
    )
)

Number of all answers =
CALCULATE (
    COUNT ( 'Table'[Answer] ),
    FILTER ( ALL ( 'Table' ), [Respondent] = SELECTEDVALUE ( 'Table'[Respondent] ) )
)

Divide = DIVIDE([Number of answer occurencies],[Number of all answers])

Since the test data you gave is not greater than 0.9, let's first assume that filtering the trigger column for values greater than 0.35.

Measure = IF([Divide]>0.35,1,0)

Put the measure into Filter and set it equal to 1.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Hi, @v-zhangti  thanks, but it is not exactly what I meant.

The result should be only the number of "rows" in column divide that are greater than 0,9.

I want to show the output only as a card in Power Bi. So there are no selected values etc. 

Do you understand? I know, that I my description is difficult, sorry.