Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 ( Tabelle1[Faktor 1] ) )
)

and the moving average with the following function:

Moving Average_Measure IndexMeasure =
VAR Window_A = 10
VAR myResult =
SUMX(
FILTER(
Tabelle1,
Tabelle1[Index_Measur 2]> Tabelle1[Index_Measur 2]-Window_A &&
Tabelle1[Index_Measur 2] <=Tabelle1[Index_Measur 2]
),Tabelle1[Wert]
)
RETURN CONVERT(myResult/Window_A,DOUBLE)

 

Tabelle1[Werte] are the values for moving average

 

I cannot create a plot Moving mean as a function of dynamic index. Where is the error?

DAX

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago
      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 1Faktor 2WertIndexMoving Average Test
      AB110.1
      BA420.5
      BA631.1
      AB741.8
      BB352.1
      AA262.3
      BB172.4
      BB582.9
      BA893.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
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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