DAX Measure to analyze survey data

[wonyango]

Dear All,

I have some survey data that I have analyzed in MS Excel but I find it a bit challenging to analyze in Power BI and I am interested in doing it in Power BI. The tutorials I have watched are about analyzing survey data where most of the questions require only one response. In this case, some questions allow a respondent to choose more than one answer. For instance, I have a question asking, "Which rights and fundamental freedoms are you aware of?" The respondent is allowed to choose more than one answer. Each respondent has a respondent ID. The total respondents for the survey are 779. 1 denotes Yes, 2 denotes No. Now, let's say I want to get the % of the respondents that ticked 'Right to Life'. It means I will take the total of 1s over the total respondents.  I have to do this for all the rights. The data is organized in a table. Now I want to get the percentages of All the rights against the total respondents and then make a bar chart, and be able to filter by County name (I have a dimension table with the demographic characteristics), is this possible in Power BI? 

Regards.

[StefanoGrimaldi]

hey, 

I would reccomend for a better answer and quick solution paste a dummy example of the data for a brief case show, them show the end result you want, 

yes this can be done, here a simple dax that would do the trick: 

Data: 

New % Measure = ( (CALCULATE(COUNT(survey[answer]),ALLEXCEPT(survey,survey[answer])))/(CALCULATE(DISTINCTCOUNT(survey[Person ID]),ALLEXCEPT(survey,survey[Person ID]))))*100

result: 

this will give the % of diferent people by each possible answer.

[wonyango]

Hi Stefano,

Thank you very much for the response. Here's a link to some subset of the data set: https://drive.google.com/file/d/1WZB1BJvafGmAuevGH0CNSr34EIiLHEK8/view

The idea is not to get the percent of different people. The idea is to get the percent for every right. For instance,For the Right to Life, 622 out of 779 answered yes which is denoted by 1, which comes to about 80%. The list has 26 rights or so. So, the DAX formula should calculate for each right the percentage, then I should be able to apply some filters. I have attached an image of how the filter should look like every time I pick any value.