Help with windows function to calculate running AVG

[Nawaz]

I'm a biginer in Power BI, so please ignore silly mistakes.
I have tried below DAX calculation to calculate the running avg using windows and it does not work.

WindowAVG =

AVERAGEX(

WINDOW(-1,REL,0,REL,

   SUMMARIZE(

            ALLSELECTED(Sheet1),

            Sheet1[category]

        )

        ),[sales]

)

Thanks,

Nawaz

[Anonymous · Accepted solution]

Hi @Nawaz 

Thanks for the reply from @Greg_Deckler , please allow me to provide another insight:

Here I create a set of sample:

Then create 2 measures:

SUM_Sales =
VAR _currentCa =
    SELECTEDVALUE ( 'Table'[Category] )
RETURN
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Category] = _currentCa )
    )

AVG =
VAR _vtable =
    SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Category], "_SUM", [SUM_Sales] )
VAR _vtable2 =
    ADDCOLUMNS ( _vtable, "_sort", RANKX ( _vtable, 'Table'[Category],, ASC ) )
VAR _vtable3 =
    ADDCOLUMNS (
        _vtable2,
        "_AVG",
            AVERAGEX (
                FILTER (
                    _vtable2,
                    [_sort] <= EARLIER ( [_sort] )
                        && [_sort]
                            >= EARLIER ( [_sort] ) - 1
                ),
                [_SUM]
            )
    )
RETURN
    MAXX (
        FILTER ( _vtable3, [Category] = SELECTEDVALUE ( 'Table'[Category] ) ),
        [_AVG]
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Nawaz Try one of these approaches. Better Rolling Average - Microsoft Fabric Community

Or this one, just replace SUMX with AVERAGEX: Better Running Total - Microsoft Fabric Community