cancel
Showing results 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

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 Received Question Answer 1/Jan/21 Question 1 Strongly Agree 1/Jan/21 Question 1 Agree 1/Feb/21 Question 1 Agree 1/Feb/21 Question 1 Agree 1/Mar/21 Question 1 Strongly Agree 1/Mar/21 Question 1 Strongly 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
Super User

Hi @robofski ,

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

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(
ALLEXCEPT(yourTable, yourTable[yourMonthField]) //Use month (or better, month/year) field instead of [Date Received]
)
VAR __selectedResponses =
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!

4 REPLIES 4
Super User

Hi @robofski ,

Your base measure will be something like this:

``````_productPercent =
VAR __totalResponses =
CALCULATE(
)
VAR __selectedResponses =
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!

Resolver II

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

And ina table it shows that:

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

Thoughts?

Super User

Hi @robofski ,

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

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(
ALLEXCEPT(yourTable, yourTable[yourMonthField]) //Use month (or better, month/year) field instead of [Date Received]
)
VAR __selectedResponses =
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!

Resolver II

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors