Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
robofski
Resolver II
Resolver II

Survey Data changes over time

Hi folks, hoping I can explain what I'm trying to do clearly enough that someone might be able to help!

 

I have survey data where respondants answer several questions with the same answers (coming from a likert question).  I end up with something like this:

Date ReceivedQuestionAnswer
1/Jan/21Question 1Strongly Agree
1/Jan/21Question 1Agree
1/Feb/21Question 1Agree
1/Feb/21Question 1Agree
1/Mar/21Question 1Strongly Agree
1/Mar/21Question 1Strongly Agree

 

What I am trying to do is show the Percentage of respondants each month who gave what answer so the results for the above would be:

Jan 50% Agree 50% Strongly Agree

Feb 100% Agree

Mar 100% Strongly Agree

 

Any pointers? 

1 ACCEPTED SOLUTION

Hi @robofski ,

 

Based on your example data, I get the following output:

BA_Pete_0-1626352620035.png

 

I suspect that in your real data the [Date Received] field isn't always the first of the month, right?

If so, then you can adjust the measure as follows:

_responsePercent =
VAR __totalResponses =
CALCULATE(
  COUNT(yourTable[Answer]),
  ALLEXCEPT(yourTable, yourTable[yourMonthField]) //Use month (or better, month/year) field instead of [Date Received]
)
VAR __selectedResponses =
COUNT(yourTable[Answer])
RETURN
DIVIDE( __selectedResponses, __totalResponses , 0)

 

As an aside: I notice that your output is only using months, not month/year. I would recommend updating the measure to using month/year in case you report over many years, in which case, the same months in different years wll be aggregated together.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
BA_Pete
Super User
Super User

Hi @robofski ,

 

Your base measure will be something like this:

 

_productPercent =
VAR __totalResponses =
CALCULATE(
  COUNT(yourTable[Answer]),
  ALLEXCEPT(yourTable, yourTable[Date Received])
)
VAR __selectedResponses =
COUNT(yourTable[Answer])
RETURN
DIVIDE( __selectedResponses, __totalResponses , 0)

 

 

Then you can just add this to a visual with Month and Answer (and Question if you want to break down this way).

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete thank you so much for the DAX.  It is however not doing what I need in that the percentage is still being calculated across all the data not just for that month.  See the sample chart below:

robofski_0-1626351311796.png

And ina table it shows that:

robofski_1-1626351470253.png

 

There are fewer results in July but I still need to caclulate the % of each answer for that month indepentantly of others months.

 

Thoughts? 

 

Hi @robofski ,

 

Based on your example data, I get the following output:

BA_Pete_0-1626352620035.png

 

I suspect that in your real data the [Date Received] field isn't always the first of the month, right?

If so, then you can adjust the measure as follows:

_responsePercent =
VAR __totalResponses =
CALCULATE(
  COUNT(yourTable[Answer]),
  ALLEXCEPT(yourTable, yourTable[yourMonthField]) //Use month (or better, month/year) field instead of [Date Received]
)
VAR __selectedResponses =
COUNT(yourTable[Answer])
RETURN
DIVIDE( __selectedResponses, __totalResponses , 0)

 

As an aside: I notice that your output is only using months, not month/year. I would recommend updating the measure to using month/year in case you report over many years, in which case, the same months in different years wll be aggregated together.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




You Sir are a legend!  Thanks a lot, you were bang on with my data being all over the month so creating a YearMonth column and using that gets me exactly what I needed.

Thanks again! 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors