Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good day
I trying to create measure with moving average of values wich was grouped (by 50 values in a group)
Index | FM Dev (%) | Group No |
1 | 0.4 | 0 |
2 | 2.3 | 0 |
3 | 1 | 0 |
4 | 1.1 | 0 |
5 | 1.3 | 0 |
6 | 1.2 | 0 |
7 | 1.7 | 0 |
8 | 0.3 | 0 |
9 | 1.1 | 0 |
10 | 1 | 0 |
11 | 2.2 | 0 |
12 | 1.8 | 0 |
13 | 2.7 | 0 |
14 | 2.6 | 0 |
15 | 0.6 | 0 |
16 | 0.9 | 0 |
17 | 0.9 | 0 |
18 | 0.8 | 0 |
19 | 1.7 | 0 |
20 | 1.6 | 0 |
21 | 2.3 | 0 |
22 | 1.4 | 0 |
23 | 0.7 | 0 |
24 | 1.1 | 0 |
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 No | FMdev | MA FM |
0 | 1.331 | 1.331 |
1 | 1.42 | 1.42 |
2 | 1.296 | 1.296 |
3 | 1.326 | 1.326 |
4 | 1.194 | 1.194 |
5 | 0.974 | 0.974 |
6 | 1.53 | 1.53 |
7 | 1.266 | 1.266 |
8 | 1.018 | 1.018 |
9 | 0.648 | 0.648 |
10 | 0.51 | 0.51 |
11 | 0.436 | 0.436 |
12 | 0.768 | 0.768 |
13 | 0.89 | 0.89 |
14 | 0.552 | 0.552 |
15 | 0.636 | 0.636 |
16 | 0.662 | 0.662 |
17 | 0.736 | 0.736 |
18 | 0.806 | 0.806 |
19 | 0.84 | 0.84 |
20 | 0.834 | 0.834 |
After that I create another table with grouped data, but resoult always the same.
Please help me to find where I was wrong?
Solved! Go to 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])))
For more details, please check the pbix as attached.
Regards,
Frank
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])))
For more details, please check the pbix as attached.
Regards,
Frank
Thank you very much. Everything works good.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |