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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Moving average by grouped values

Good day

I trying to create measure with moving average of values wich was grouped (by 50 values in a group)

 

IndexFM Dev (%)Group No
10.40
22.30
310
41.10
51.30
61.20
71.70
80.30
91.10
1010
112.20
121.80
132.70
142.60
150.60
160.90
170.90
180.80
191.70
201.60
212.30
221.40
230.70
241.10

etc
Than I create measure like in many advises in this community

 

MA FM = CALCULATE(SUM(FM[FM Dev (%)]);FILTER(FM; FM[Group No] > FM[Group No] - 3 && FM[Group No] <= FM[Group No]))

But result looks not good:

Group NoFMdevMA FM
01.3311.331
11.421.42
21.2961.296
31.3261.326
41.1941.194
50.9740.974
61.531.53
71.2661.266
81.0181.018
90.6480.648
100.510.51
110.4360.436
120.7680.768
130.890.89
140.5520.552
150.6360.636
160.6620.662
170.7360.736
180.8060.806
190.840.84
200.8340.834

 

After that I create another table with grouped data, but resoult always the same.

 

Please help me to find where I was wrong?

 

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

Please try to update your formula as below.

 

MA FM = CALCULATE(SUM(FM[FM Dev (%)]),FILTER(ALL(FM), FM[Group No] > MAX(FM[Group No]) - 3 && FM[Group No] <= MAX(FM[Group No])))

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Just found mistake in DAX formula

of course this is AVERAGE instead SUM

Hi @Anonymous,

 

Please try to update your formula as below.

 

MA FM = CALCULATE(SUM(FM[FM Dev (%)]),FILTER(ALL(FM), FM[Group No] > MAX(FM[Group No]) - 3 && FM[Group No] <= MAX(FM[Group No])))

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Thank you very much. Everything works good.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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