topic Dynamic calculation moving average depending on filter in DAX Commands and Tips

Dynamic calculation moving average depending on filter

Anonymous — Thu, 26 May 2022 16:59:09 GMT

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?

Re: Dynamic calculation moving average depending on filter

amitchandak — Fri, 27 May 2022 08:23:53 GMT

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/

Re: Dynamic calculation moving average depending on filter

Anonymous — Fri, 27 May 2022 16:22:28 GMT

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

Re: Dynamic calculation moving average depending on filter

Anonymous — Thu, 02 Jun 2022 02:52:38 GMT

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