Survey Data changes over time

[robofski]

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? 

[BA_Pete]

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

[robofski]

@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? 

[BA_Pete · Accepted solution]

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

[robofski]

You Sir are a legend!  Thanks a lot, you were bang on with my data being all over the month so creating a YearMonth column and using that gets me exactly what I needed.

Thanks again!