Dynamic calculation moving average depending on filter

[Anonymous]

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?

[amitchandak]

@Anonymous , Can please explain the logic here

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if this can help

https://exceleratorbi.com.au/pareto-analysis-in-power-bi/

[Anonymous]

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

[Anonymous]

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