Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculating a rolling average for percentages

Hi,

I’m very new to DAX so would appreciate any help on this one!

I have Overall Satisfaction data measured on a scale of 1 (Very dissatisfied) to 7 (Very satisfied). I have bucketed this data by creating a a separate measure for 3 groups: T2B (scores 6&7); M3B (scores 3to5) and B2B (scores 1&2), where the measures are percentages of the total base.

 
Syntax for first measure:
OVERALL CSAT_T2B =
DIVIDE (
CALCULATE ( COUNT ('testdata'[Overall Sat]), 'testdata'[Overall Sat] >= 6 ),
COUNT ('testdata'[Overall Sat] )
)

 

The results for the 3 groups are shown over a 6-month period:

 

 

I want to smooth out the trendlines using a 3-month rolling average. I can create an overall 3-month rolling average based on the actual Overall Satisfaction score but I can’t figure out how to do it based on percentages for each group. Or perhaps there's a different way of calculating the rolling average for percentages? Many thanks!

Need Help

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for the suggestion about using a Quick Measure. I had tried that but got an error message 'Only Power BI - provided date hierarchies are supported' (although, using the same Date field in a Month over Month change measure worked.

       

      I found the following syntax on another discussion thread:

      OVERALL CSAT AVG_3MTH_MA =

      CALCULATE (

          AVERAGEX ( 'testdata', 'testdata'[Overall Sat] ),

          DATESINPERIOD (

              'DateKey'[Date],

              LASTDATE ( 'DateKey'[Date] ),

              -3,

              MONTH

          )

      )

       

      I've used this successfully to create a rolling 3-month average for the score but I can't create the rolling average for the percentage buckets (as described in my first post) correctly. I'm not sure of the syntax I should be using to filter the scores for each bucket. 

       

      I'd really appreciate any help on this one! TIA.