How to create Clustered column chart to show %ge change between current and previous months

[AVS218]

I have this attached visual generated based on the data set example shown below for 5 survey questions. I have two  request from business to see

1. if I can generate a similar visual displaying %ge movement of responses between months. i.e. %ge respondents moving from "I don't know" to "Agree" between Nov'24 to Dec'24 to Feb'25
2. Alternative best visual to show %ge respondents per scale and what trend we see when compared between months per function

Thanks in advance for the support. Really appreciate as this request has come to me at very short notice.

Function	Country	Survey Month	Question 1	Question 2
ABC	UK	Nov'24	Agree	Agree
DEF	US	Nov'24	Strongly Agree	Strongly Agree
ABC	Germany	Nov'24	I Don't Know	I Don't Know
GHI	UK	Dec'24	Disagree	Disagree
GHI	US	Dec'24	Strongly Disagree	Strongly Disagree
DEF	Germany	Dec'24	Agree	Agree
ABC	Sweden	Jan'25	Strongly Agree	Strongly Agree
DEF	Spain	Jan'25	Strongly Disagree	Strongly Disagree
ABC	Sweden	Jan'25	I Don't Know	I Don't Know

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result in a table format very clearly.  Once we get the correct figures in a table, we should be able to build our desired visual.

[bhanu_gautam]

@AVS218 Try below steps

You need to create a calculated column that captures the responses from the previous month for each question. This can be done using DAX (Data Analysis Expressions).
DAX
PreviousMonthResponseQ1 =
CALCULATE(
MAX('Table'[Question 1]),
FILTER(
'Table',
'Table'[Function] = EARLIER('Table'[Function]) &&
'Table'[Country] = EARLIER('Table'[Country]) &&
'Table'[Survey Month] = EOMONTH(EARLIER('Table'[Survey Month]), -1)
)

Repeat this for each question.

Calculate Percentage Change:
Create another calculated column to calculate the percentage change between the current and previous month responses.

DAX
PercentageChangeQ1 =
IF(
ISBLANK('Table'[PreviousMonthResponseQ1]),
BLANK(),
DIVIDE(
COUNTROWS(FILTER('Table', 'Table'[Question 1] <> 'Table'[PreviousMonthResponseQ1])),
COUNTROWS('Table')
)

Repeat this for each question.

Create the Clustered Column Chart:

In Power BI, select the clustered column chart from the visualizations pane.
Drag the Survey Month to the Axis field.
Drag the PercentageChangeQ1 (and other percentage change columns) to the Values field.
Optionally, you can add Function or Country to the Legend field to differentiate between different functions or countries.

[AVS218]

Aparently it is throwing blanks in the Calculated Columns. 

Also when I need to plot the cluster chart I need to show scale on X-Axis, and %ge change between month bars as shown in the original screenshot of cluster chart I shared. and this need to be managed with filters so that If I filter by "Function" or "Country" it shows similar plot but at "Function" or "Country" level