Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table with the following information:
Index | Iteration | Points |
1 | 38 | 19 |
2 | 39 | 16 |
3 | 40 | 13 |
4 | 41 | 15 |
5 | 42 | 16 |
6 | 43 | 16 |
I need a double while/for to create the average of the last 3 iterations to a new table: (Example)
Index | Iteration | Average |
1 | 40 | = (19+16+13) / 3 |
2 | 41 | = (16+13+15) / 3 |
3 | 42 | = (13+15+16) / 3 |
4 | 43 | = (15+16+16) / 3 |
I think recursion is not possible in Dax Code but I've tried different things to see a workaround.
Please help
Solved! Go to Solution.
Hi @donatoath,
If you would like to achieve the desired result with a DAX formula, you may try this Measure.
Avg =
VAR avg_ =
CALCULATE (
AVERAGE ( 'Table'[Points] ),
FILTER (
ALL ( 'Table' ),
'Table'[Iteration] >= MAX ( 'Table'[Iteration] )
&& 'Table'[Iteration]
< ( MAX ( 'Table'[Iteration] ) + 3 )
)
)
VAR maxIndex =
CALCULATE ( MAX ( 'Table'[Index] ), ALL ( 'Table' ) )
RETURN
IF ( MAX ( 'Table'[Index] ) > maxIndex - 2, BLANK (), avg_ )
Then, the result looks like this.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Hi @donatoath,
If you would like to achieve the desired result with a DAX formula, you may try this Measure.
Avg =
VAR avg_ =
CALCULATE (
AVERAGE ( 'Table'[Points] ),
FILTER (
ALL ( 'Table' ),
'Table'[Iteration] >= MAX ( 'Table'[Iteration] )
&& 'Table'[Iteration]
< ( MAX ( 'Table'[Iteration] ) + 3 )
)
)
VAR maxIndex =
CALCULATE ( MAX ( 'Table'[Index] ), ALL ( 'Table' ) )
RETURN
IF ( MAX ( 'Table'[Index] ) > maxIndex - 2, BLANK (), avg_ )
Then, the result looks like this.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
A new column
Var _sum = sumx(filter(Table, [Index]>= earlier([Index]) && [Index]<= earlier([Index]) +2) , [Points] )
Var _cnt = countx(filter(Table, [Index]>= earlier([Index]) && [Index]<= earlier([Index]) +2) , [Points] )
return
divide(_sum,_cnt)
If needed you can check for _cnt > 2
User | Count |
---|---|
42 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |