Charting multiple responses questions

[MySurveyHuB]

Hello Dears,

We have a multiple response survey question exported as single column for each answer as 0 if the answer not selected and the answer label if selected.

We need to analysis/chart this question as one question based on number of respondend not the number of responses percentages as below

Id  q1_1  q1_2  q1_3

1   A       0      C

2   A      B       0

3   A      0       0          

4   0      B       0

as we can see here, we have 4 respondents, A is counted 3 times, B two times, and C one time.

we need a chart the present Q1 as A is 75%(3/4), B is 50%(2/4), and C is 25%(1/4)

Thanks in advance.

Mahmoud

[Vilmar · Accepted solution]

Veja se ajuda:

• Transformar colunas em linhas no Power Query;
• Fazer a medida abaixo.

Medida = 
DIVIDE ( 
    COUNTROWS ( Tabela ), 
    COUNTROWS ( ALL ( Tabela[Valor] ) )
)

 

[MySurveyHuB]

SUPERRRRRRRRRR

thanks a lot

[MySurveyHuB]

We need the output to be like this

[MySurveyHuB]

[Greg_Deckler]

@MySurveyHuB Go into Power Query Editor. Right-click your Id column and choose Unpivot other columns. At this point the calculation should become trivial such as:

Percent Responded Measure =
  VAR __Table = 'Table'
  VAR __NumberAnswered = FILTER(__Table, [Value] <> 0)
  VAR __TotalNumber = COUNTROWS(__Table)
  VAR __Result = DIVIDE( __NumberAnswered, __TotalNumber )
RETURN
  __Result

[MySurveyHuB]

@Greg_Deckler i got this message in the measure calcuation

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[Greg_Deckler]

@MySurveyHuB Apologies, missed a COUNTROWS:

Percent Responded Measure =
  VAR __Table = 'Table'
  VAR __NumberAnswered = COUNTROWS(FILTER(__Table, [Value] <> 0))
  VAR __TotalNumber = COUNTROWS(__Table)
  VAR __Result = DIVIDE( __NumberAnswered, __TotalNumber )
RETURN
  __Result

[MySurveyHuB]