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

[datasetleo]

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!

[v-frfei-msft · Accepted solution]

Hi @datasetleo ,

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 )

[ImkeF]

This article describes how to create a rolling sum, just use List.Average instead: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power...