Forum Discussion
Dynamic calculation moving average depending on filter
I have the following problem: I have calculated a dynamic measure index: Index_Measur 2 = CALCULATE ( COUNTROWS ( Tabelle1 ), FILTER ( ALLSELECTED ( Tabelle1 ), Tabelle1[Faktor 1] < MAX ( Tabe...
Hallo amitchandak,
thanks for the reply
I would like to calculate and display the moving average depending on the filter, e.g. factor 2 = A.
This are only test data!
| Faktor 1 | Faktor 2 | Wert | Index | Moving Average Test |
| A | B | 1 | 1 | 0.1 |
| B | A | 4 | 2 | 0.5 |
| B | A | 6 | 3 | 1.1 |
| A | B | 7 | 4 | 1.8 |
| B | B | 3 | 5 | 2.1 |
| A | A | 2 | 6 | 2.3 |
| B | B | 1 | 7 | 2.4 |
| B | B | 5 | 8 | 2.9 |
| B | A | 8 | 9 | 3.7 |
This are the basic data!
The index is calculate with:
Index = RANKX(Tabelle1,Tabelle1[Index],,ASC)
The moving average with:
Moving Average Test =
VAR MyIndex = Tabelle1[Index]
VAR Window_A = 10
VAR myResult =
SUMX(
FILTER(
Tabelle1,
Tabelle1[Index] > MyIndex-Window_A &&
Tabelle1[Index] <= MyIndex
),Tabelle1[Wert]
)
RETURN FIXED(myResult/Window_A ,2)
I would now like to calculate moving average, e.g. if A is selected in column Factor 2.
I want to create a plot: Moving average as a function of the new dynamic index.
PREVIEW
Friedbert
HI Anonymous,
Did you mean the 'window A' part should be replaced with the current selection from the filter?
If that is the case, you can create an unrelated table for all needed selection values and not have a relationship to the current table.
Then you can use the Dax function to get sections from the filter/slicer to interact with current expressions.
formula =
VAR Window_A =
MAX ( NewTable[Value] )
VAR myResult =
CALCULATE (
SUM ( Tabelle1[Wert] ),
FILTER (
ALLSELECTED ( Tabelle1 ),
Tabelle1[Index_Measur 2] > Tabelle1[Index_Measur 2] - Window_A
&& Tabelle1[Index_Measur 2] <= Tabelle1[Index_Measur 2]
)
)
RETURN
CONVERT ( myResult / Window_A, DOUBLE )Regards,
Xiaoxin Sheng