Counting number of responses per calendar month for three response types

[Anonymous]

Hi guys, I have a data set from which I'd like to count the number of responses per calendar month but divided into three different response types (agree, disagree and neutral). So my end result would look something like this: 

 My source data table looks like this: 

Date Submitted	Response	Date Submitted (bins)
16/04/2021 13:00	Agree	Apr-21
18/09/2020 11:37	Agree	Sep-20
16/04/2021 13:46	Agree	Apr-21
18/09/2020 11:17	Agree	Sep-20
16/04/2021 14:58	Agree	Apr-21
06/04/2021 08:50	Agree	Apr-21

I'm quite a beginner in PowerBI so I'm not sure if this can be achieved using calculated columns or if I need to create a calculated table from my data? Thanks for any guidance!

[Fowmy]

@Anonymous 

You can create three measures, I created for Agree below, do the same for the rest two. Replace the Table Name with yours

Agree = 
CALCULATE (
    COUNTROWS ( TableName ),
    TableName[Response] = "Agree"
)

[Anonymous]

Hi @Fowmy , thank you for the reply. The problem with using measures is that I'm trying to show an average line for each response type in a line and stacked column chart (see below) and it doesn't seem that you can calculate average automatically from the measure in the graph settings? 

 Thank you!

[Fowmy]

@Anonymous 

I answered based on your initial question. Now it seems you have a different issue. ,So, you need to show a stacked column chart for the count of each response and to show three average lines for each of them?

[Anonymous]

Hi @Fowmy , yes that is correct. Apologies, I should've added that detail in my initial question. Thank you

[Fowmy]

@Anonymous 

You are using a Line and Stacked Column Chart here, you can create three more measures to calculate the average and add them in the Line Values Section. You can use the [Agree] measure that you created above within the Calculate below. 

Agree Avg = 
CALCULATE(
    [Agree],
    ALLSELECTED(TableName[Date Submitted (Bin)]),
    TableName[Response] = "Agree"
)

[Anonymous]

Hi @Fowmy ,

I created a new measure for Agree Avg as you described and added it to the line values but this line seems to be indicating the total agree responses per month rather than the average? Perhap it's me not being clear again, the average I'm looking for is the average across all the months, therefore as new months are added to the model the average line for each response type is adjusted. Thank you for your help so far

[Fowmy · Accepted solution]

@Anonymous 

I am not sure about your data model, so I created a dummy data set based on your sample and did the following. Please check the attached file.

[Anonymous]

Hi @Fowmy , thank you for sending the file. I noticed you used the AVERAGEX function to calculate the averages. That did the trick, thanks for your help.