Month over Month analysis for survey Data

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,