Month over Month analysis for survey Data

[jambopriti]

Hello,

I'm a new bee and learning Power BI.

I have a survey data where user needs only top 2 responses month over month analysis.

Ex: in the data Q 111, combine count of top 2 response (Very satisfied, satisfied) and convert into %.

The targeted % value is 80%

I need the month over month analysis of % of top 2 responses combine in a graph with creation_date slicer. Please guide. the test data is included. Thanks.

ex: 

Question ID	Respondant_id	creation_date	Creation_Date_Formatted	question	answer
111	5751	2024-08-29T07:23:12.000	2024-08-29	How satisfied are you with the web site experience?	Very Satisfied
112	5751	2024-08-29T07:23:12.000	2024-08-29	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Very easy
111	5752	2024-08-29T16:04:51.000	2024-08-29	How satisfied are you with the web site experience?	Satisfied
112	5752	2024-08-29T16:04:51.000	2024-08-29	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Very easy
111	5753	2024-08-29T21:20:46.000	2024-08-29	How satisfied are you with the web site experience?	Very Satisfied
112	5753	2024-08-29T21:20:46.000	2024-08-29	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Very easy
111	5760	2024-08-30T20:16:49.000	2024-08-30	How satisfied are you with the web site experience?	Very Satisfied
112	5760	2024-08-30T20:16:49.000	2024-08-30	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Easy
111	5761	2024-08-31T12:37:10.000	2024-08-31	How satisfied are you with the web site experience?	Satisfied
112	5761	2024-08-31T12:37:10.000	2024-08-31	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Neither easy nor difficult
111	5762	2024-09-01T10:04:04.000	2024-09-01	How satisfied are you with the web site experience?	Very Satisfied
112	5762	2024-09-01T10:04:04.000	2024-09-01	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Very easy
111	5763	2024-09-03T07:35:49.000	2024-09-03	How satisfied are you with the web site experience?	Neutral
112	5763	2024-09-03T07:35:49.000	2024-09-03	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Easy
111	5764	2024-09-03T08:55:29.000	2024-09-03	How satisfied are you with the web site experience?	Very Satisfied
112	5764	2024-09-03T08:55:29.000	2024-09-03	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Very easy
111	5767	2024-09-04T10:10:18.000	2024-09-04	How satisfied are you with the web site experience?	Very Satisfied
112	5767	2024-09-04T10:10:18.000	2024-09-04	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Very easy
111	5769	2024-09-04T19:21:48.000	2024-09-04	How satisfied are you with the web site experience?	Satisfied
112	5769	2024-09-04T19:21:48.000	2024-09-04	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Difficult
111	5770	2024-09-05T03:09:33.000	2024-09-05	How satisfied are you with the web site experience?	Very Satisfied
112	5770	2024-09-05T03:09:33.000	2024-09-05	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Very easy
111	5772	2024-09-09T09:09:38.000	2024-09-09	How satisfied are you with the web site experience?	Satisfied
112	5772	2024-09-09T09:09:38.000	2024-09-09	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Neither easy nor difficult
111	5775	2024-09-10T15:16:03.000	2024-09-10	How satisfied are you with the web site experience?	Very Satisfied
112	5775	2024-09-10T15:16:03.000	2024-09-10	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Very easy
111	5776	2024-09-11T08:24:41.000	2024-09-11	How satisfied are you with the web site experience?	Satisfied
112	5776	2024-09-11T08:24:41.000	2024-09-11	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Neither easy nor difficult
111	5778	2024-09-13T00:00:55.000	2024-09-13	How satisfied are you with the web site experience?	Very Satisfied
112	5778	2024-09-13T00:00:55.000	2024-09-13	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Easy
111	5779	2024-09-14T04:28:49.000	2024-09-14	How satisfied are you with the web site experience?	Very Satisfied
112	5779	2024-09-14T04:28:49.000	2024-09-14	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Very easy
111	5780	2024-09-15T07:51:52.000	2024-09-15	How satisfied are you with the web site experience?	Very Satisfied
112	5780	2024-09-15T07:51:52.000	2024-09-15	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Neither easy nor difficult
111	5781	2024-09-16T00:32:06.000	2024-09-16	How satisfied are you with the web site experience?	Satisfied
112	5781	2024-09-16T00:32:06.000	2024-09-16	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Easy
111	5783	2024-09-16T18:14:51.000	2024-09-16	How satisfied are you with the web site experience?	Very Satisfied
112	5783	2024-09-16T18:14:51.000	2024-09-16	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Very easy
111	5785	2024-09-18T10:40:47.000	2024-09-18	How satisfied are you with the web site experience?	Satisfied
112	5785	2024-09-18T10:40:47.000	2024-09-18	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Easy
111	5787	2024-09-19T22:05:19.000	2024-09-19	How satisfied are you with the web site experience?	Neutral
112	5787	2024-09-19T22:05:19.000	2024-09-19	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Neither easy nor difficult
111	5788	2024-09-20T01:51:01.000	2024-09-20	How satisfied are you with the web site experience?	Very Satisfied
112	5788	2024-09-20T01:51:01.000	2024-09-20	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Easy
111	5789	2024-09-23T02:48:04.000	2024-09-23	How satisfied are you with the web site experience?	Neutral
112	5789	2024-09-23T02:48:04.000	2024-09-23	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Neither easy nor difficult
111	5790	2024-09-23T12:41:25.000	2024-09-23	How satisfied are you with the web site experience?	Very Satisfied
112	5790	2024-09-23T12:41:25.000	2024-09-23	With your intended purpose in mind, how easy or difficult was it to find / do what you were hoping  to on the site today?	Very difficult
111	5791	2024-09-23T14:22:50.000	2024-09-23	How satisfied are you with the web site experience?	Very Satisfied

[DataNinja777 · Accepted solution]

Hi @jambopriti ,

To perform a month-over-month analysis of the top two responses in your survey data using Power BI, start by importing the dataset and ensuring that a date table is available for time-based calculations. Since we are only interested in question 111, which asks, "How satisfied are you with the website experience?", the first step is to create a measure that counts the responses where the answer is either "Very Satisfied" or "Satisfied". This can be achieved using the following DAX formula:

Top2ResponsesCount = 
CALCULATE(
    COUNT('Survey Data'[Respondant_id]),
    'Survey Data'[question] = "How satisfied are you with the web site experience?",
    'Survey Data'[answer] IN {"Very Satisfied", "Satisfied"}
)

Next, we need a measure that calculates the total number of responses for question 111 in each month. This will serve as the denominator for calculating the percentage of top responses.

TotalResponses = 
CALCULATE(
    COUNT('Survey Data'[Respondant_id]),
    'Survey Data'[question] = "How satisfied are you with the web site experience?"
)

To determine the percentage of top two responses per month, divide the count of top responses by the total responses using the following measure:

Top2ResponsesPercentage = 
DIVIDE([Top2ResponsesCount], [TotalResponses], 0)

Now, to analyze the month-over-month change in this percentage, we create another measure that calculates the difference between the current month’s percentage and the previous month’s percentage.

MoM_Change = 
VAR CurrentMonth = [Top2ResponsesPercentage]
VAR PreviousMonth = CALCULATE(
    [Top2ResponsesPercentage], 
    PREVIOUSMONTH('DateTable'[Date])
)
RETURN 
IF(NOT(ISBLANK(PreviousMonth)), CurrentMonth - PreviousMonth, BLANK())

With these measures in place, you can create a line chart where the X-axis represents the Creation_Date_Formatted in a month-year format, and the Y-axis represents the Top2ResponsesPercentage. To enhance the visualization, add a target line at 80% from the analytics pane. A slicer can be included to allow filtering by the Creation_Date_Formatted field, providing an interactive way to analyze the trends over time. This approach enables a clear visualization of whether user satisfaction is meeting or exceeding the 80% target month-over-month.

Best regards,

[jambopriti]

Thank you so much DataNinja777.

I love the way you explained section by section and that is so helpful for new-bee like me. Appreciate it.

I followed and not getting the result I need. I have the test.pbix created and don't know how to post it. would you please guide and also if you can check the pbix I created? Thank you.