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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Averaging a Measure

I have a table with survey results. I also have a date table with an inactive realationship setup between the 2 using the survey date from the survey results table and the date from the table. The Month End column below is from my date table and the positive % measure is from my survey results table:

1.PNG 

 

My positive % measure consist of these pieces:
1.PNG

1.PNG 

1.PNG

 

A certain set of values represents positive feedback (happy, very happy, agree , strongly agree). Anything else would either be considered netural or negative feedback (neutral, unhappy, very unhappy, disagree, strongly disagree). So the focus here is, out of all feedback given, what % represents positive feedback. Months where there are no %'s just means no surveys were taken that month.

 

I was asked to turn this data into a trailing 12 month average, but I'm not sure how. I had success with a different set of data by turning my measure into a column then building my trailing 12 month average measure, but it is not working with this scenario. When I turn the above measure into a column so I can use it with the AVERAGE function, it returns the total count of "positive" feedback on each row rather than the percentages:

1.PNG

With it coming out this way, I'm unable to build my trailing 12 month retention formula. It returns 100% for every month and I know that's not right. Any ideas of how I can set this up?

 

 

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

Could you please illustrate "trailing 12 month average" with examples? Do you mean you want to get the averate from 1/31/2017 to 1/31/2018 on 1/31/2018, and get average from 3/31/2017 to 3/31/2018 on 3/31/2018, etc? 

How about trying this formula

Measure2 = AVERAGEX(SUMMARIZE(ALL Survey Data, ALL Survey Data [MonthEnd], "12month", [ positive %]), [ positive %])

Additionally, here is a similar post.

Calculate Average Trailing 12 Months

 

 

Best Regards

Maggie

Anonymous
Not applicable

@v-juanli-msft yes, that's exactly what I mean! One small issue. My [Month End] column is coming from my Date table, not my All Survey Data table. How could I modify it to reference that? Or should I just make a month end column from my All Survey Data table? I wasn't sure if it was possible since there are gaps everywhere in that table with the dates.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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