Forum Discussion

Nawaz's avatar
Nawaz
Frequent Visitor
2 years ago
Solved

Help with windows function to calculate running AVG

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.