DatamindsMBM
New Member
- Joined
- Oct 7, 2016
- Messages
- 1
Hello Community,
I am making a report displaying survey data. There are scalar questions, multi questions, and categorical questions. I'd like to make fractions across these questions in one table using DAX measures. I'd like to know the percentage of replies on both categorical and multi questions. I have no issues reporting on scalar values, and it's pretty simple with categorical questions as they are binary, and can only sum to 100%. but multi questions can sum to more than 100%.
An example: I want to examine my distributors. Perhaps the knowledge of my product. So my multi question is "Distributor knowledge about my product". The possible responses are:
- Knows how to install my product
- Knows how to use additionally accessory for my product
- Knows the difference between my product and my competitors product
- Has seen my particular demonstration of my product
- Knows how my product is produced.
- Owns the product privately.
One distributor might mark 4 of them, another might mark all of them, and a third might only mark 2. Nevertheless there will only be 3 respondents, but 4+6+2=11 reponses, 12/3=4. However there can only be 100% or less within each question, so I would like to know the percentage within each response. for example 2 respondents responded with "Knows how my product is produced". That makes it 2/3=0,66, since there were 3 respondents. Next step is to visualize all of this in a table. Now I did find a way to do this with DAX measures.
"Count of answer" = COUNTROWS(ReportAnswer) and
"Count of completed surveys" = COUNTROWS(ALLSELECTED(ReportSurveyRespondent[TimeCompleted])).
Then writing "% of Total" = Count of answer / Count of compelted sureys.
There is a problem here though. In the report I would like to filter on different distributors, but I am not able to do that, because I used the ALLSELECTED-function. And I cannot find any other way to do the "% of Total", so that I might filter anyway. If I use Count of completed = DISTINCTCOUNT(ReportSurveyRespondent[TimeCompleted]), I get responses/respondents=1 every time, because I filter between the questions, when I insert them into a table.
Pew, that was a lot. I hope you guys can help me out. Picture below of what I have now, and how I would like it to look like.
I am making a report displaying survey data. There are scalar questions, multi questions, and categorical questions. I'd like to make fractions across these questions in one table using DAX measures. I'd like to know the percentage of replies on both categorical and multi questions. I have no issues reporting on scalar values, and it's pretty simple with categorical questions as they are binary, and can only sum to 100%. but multi questions can sum to more than 100%.
An example: I want to examine my distributors. Perhaps the knowledge of my product. So my multi question is "Distributor knowledge about my product". The possible responses are:
- Knows how to install my product
- Knows how to use additionally accessory for my product
- Knows the difference between my product and my competitors product
- Has seen my particular demonstration of my product
- Knows how my product is produced.
- Owns the product privately.
One distributor might mark 4 of them, another might mark all of them, and a third might only mark 2. Nevertheless there will only be 3 respondents, but 4+6+2=11 reponses, 12/3=4. However there can only be 100% or less within each question, so I would like to know the percentage within each response. for example 2 respondents responded with "Knows how my product is produced". That makes it 2/3=0,66, since there were 3 respondents. Next step is to visualize all of this in a table. Now I did find a way to do this with DAX measures.
"Count of answer" = COUNTROWS(ReportAnswer) and
"Count of completed surveys" = COUNTROWS(ALLSELECTED(ReportSurveyRespondent[TimeCompleted])).
Then writing "% of Total" = Count of answer / Count of compelted sureys.
There is a problem here though. In the report I would like to filter on different distributors, but I am not able to do that, because I used the ALLSELECTED-function. And I cannot find any other way to do the "% of Total", so that I might filter anyway. If I use Count of completed = DISTINCTCOUNT(ReportSurveyRespondent[TimeCompleted]), I get responses/respondents=1 every time, because I filter between the questions, when I insert them into a table.
Pew, that was a lot. I hope you guys can help me out. Picture below of what I have now, and how I would like it to look like.
