topic Re: Counting number of responses per calendar month for three response types in DAX Commands and Tips

Counting number of responses per calendar month for three response types

Anonymous — Mon, 25 Oct 2021 11:32:00 GMT

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!

Re: Counting number of responses per calendar month for three response types

Fowmy — Mon, 25 Oct 2021 11:35:42 GMT

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" )

Re: Counting number of responses per calendar month for three response types

Anonymous — Mon, 25 Oct 2021 11:56:04 GMT

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!

Re: Counting number of responses per calendar month for three response types

Fowmy — Mon, 25 Oct 2021 12:07:21 GMT

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?

Re: Counting number of responses per calendar month for three response types

Anonymous — Mon, 25 Oct 2021 12:08:56 GMT

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

Re: Counting number of responses per calendar month for three response types

Fowmy — Mon, 25 Oct 2021 12:18:01 GMT

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" )

Re: Counting number of responses per calendar month for three response types

Anonymous — Mon, 25 Oct 2021 12:37:31 GMT

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

Re: Counting number of responses per calendar month for three response types

Fowmy — Mon, 25 Oct 2021 13:25:08 GMT

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.

Re: Counting number of responses per calendar month for three response types

Anonymous — Tue, 26 Oct 2021 09:39:34 GMT

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.