Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Excel - Get & Transform (Power Query) - Moving average grouped data w/ DAX

Hi,   Hi, Can someone assist on moving average when data is grouped? below is an example: looking for moving average on data grouped by "item" column.   Thanks!  
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    We can create a measure as below by DAX.

    Measure = 
VAR a =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Monthno] <= MAX ( 'Table'[Monthno] ) ),
        VALUES ( 'Table'[item] )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Monthno] <= MAX ( 'Table'[Monthno] ) ),
        VALUES ( 'Table'[item] )
    )
RETURN
    DIVIDE ( a, b )

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous ,

 

We can create a measure as below by DAX.

Measure = 
VAR a =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Monthno] <= MAX ( 'Table'[Monthno] ) ),
        VALUES ( 'Table'[item] )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Monthno] <= MAX ( 'Table'[Monthno] ) ),
        VALUES ( 'Table'[item] )
    )
RETURN
    DIVIDE ( a, b )