cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Counting number of responses per calendar month for three response types

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: 

geekface101_0-1635160898168.png

 My source data table looks like this: 

Date SubmittedResponseDate Submitted (bins)
16/04/2021 13:00AgreeApr-21
18/09/2020 11:37AgreeSep-20
16/04/2021 13:46AgreeApr-21
18/09/2020 11:17AgreeSep-20
16/04/2021 14:58AgreeApr-21
06/04/2021 08:50AgreeApr-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!

1 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.

Fowmy_0-1635168265801.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

8 REPLIES 8
Fowmy
Super User
Super User

@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"
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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? 

 

geekface101_0-1635162871981.png

 

 Thank you!

@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?

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

@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.

Fowmy_0-1635168265801.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!