Forum Discussion
Survey Data changes over time
Hi folks, hoping I can explain what I'm trying to do clearly enough that someone might be able to help!
I have survey data where respondants answer several questions with the same answers (coming from a likert question). I end up with something like this:
| Date Received | Question | Answer |
| 1/Jan/21 | Question 1 | Strongly Agree |
| 1/Jan/21 | Question 1 | Agree |
| 1/Feb/21 | Question 1 | Agree |
| 1/Feb/21 | Question 1 | Agree |
| 1/Mar/21 | Question 1 | Strongly Agree |
| 1/Mar/21 | Question 1 | Strongly Agree |
What I am trying to do is show the Percentage of respondants each month who gave what answer so the results for the above would be:
Jan 50% Agree 50% Strongly Agree
Feb 100% Agree
Mar 100% Strongly Agree
Any pointers?
Hi robofski ,
Based on your example data, I get the following output:
I suspect that in your real data the [Date Received] field isn't always the first of the month, right?
If so, then you can adjust the measure as follows:
_responsePercent = VAR __totalResponses = CALCULATE( COUNT(yourTable[Answer]), ALLEXCEPT(yourTable, yourTable[yourMonthField]) //Use month (or better, month/year) field instead of [Date Received] ) VAR __selectedResponses = COUNT(yourTable[Answer]) RETURN DIVIDE( __selectedResponses, __totalResponses , 0)As an aside: I notice that your output is only using months, not month/year. I would recommend updating the measure to using month/year in case you report over many years, in which case, the same months in different years wll be aggregated together.
Pete
4 Replies
Hi robofski ,
Your base measure will be something like this:
_productPercent = VAR __totalResponses = CALCULATE( COUNT(yourTable[Answer]), ALLEXCEPT(yourTable, yourTable[Date Received]) ) VAR __selectedResponses = COUNT(yourTable[Answer]) RETURN DIVIDE( __selectedResponses, __totalResponses , 0)Then you can just add this to a visual with Month and Answer (and Question if you want to break down this way).
Pete
BA_Pete thank you so much for the DAX. It is however not doing what I need in that the percentage is still being calculated across all the data not just for that month. See the sample chart below:
And ina table it shows that:
There are fewer results in July but I still need to caclulate the % of each answer for that month indepentantly of others months.
Thoughts?
Hi robofski ,
Based on your example data, I get the following output:
I suspect that in your real data the [Date Received] field isn't always the first of the month, right?
If so, then you can adjust the measure as follows:
_responsePercent = VAR __totalResponses = CALCULATE( COUNT(yourTable[Answer]), ALLEXCEPT(yourTable, yourTable[yourMonthField]) //Use month (or better, month/year) field instead of [Date Received] ) VAR __selectedResponses = COUNT(yourTable[Answer]) RETURN DIVIDE( __selectedResponses, __totalResponses , 0)As an aside: I notice that your output is only using months, not month/year. I would recommend updating the measure to using month/year in case you report over many years, in which case, the same months in different years wll be aggregated together.
Pete
