Forum Discussion

PBI_37's avatar
PBI_37
Icon for Helper I rankHelper I
1 month ago
Solved

Average and Moving Average

Hello everyone, I have a dataset with products and batches. In a visual such as a table, I would like to calculate the average and the moving average of the last 10 values. I have an issue with th...
  • johnt75's avatar
    johnt75
    1 month ago

    You can use the WINDOW function to get the moving average.

    Moving Average =
VAR _Base =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            'Calendar'[Date],
            dProductBatch[Batch],
            "@value", [Total Value]
        ),
        ALLSELECTED ( 'Calendar' ),
        ALL ( dProductBatch )
    )
VAR _Window =
    WINDOW (
        -10,
        REL,
        0,
        REL,
        _Base,
        ORDERBY ( 'Calendar'[Date], ASC, dProductBatch[Batch], ASC )
    )
VAR Result =
    AVERAGEX ( _Window, [@value] )
RETURN
    Result
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
1 month ago

Hi, 

I am not sure if I understood your question correctly, but please check the attached file down below.

Average = 
VAR _t =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            'Calendar'[Date],
            dProductBatch[Batch],
            "@totalvalue", [Total Value]
        ),
        ALLSELECTED ()
    )
RETURN
    IF ( NOT ISBLANK ( [Total Value] ), AVERAGEX ( _t, [@totalvalue] ) )

 

10 days moving Average = 
VAR _t =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            'Calendar'[Date],
            dProductBatch[Batch],
            "@totalvalue", [Total Value]
        ),
        ALLSELECTED ()
    )
VAR _moving =
    WINDOW ( 0, REL, 9, REL, _t, ORDERBY ( 'Calendar'[Date], DESC ) )
RETURN
    IF ( NOT ISBLANK ( [Total Value] ), AVERAGEX ( _moving, [@totalvalue] ) )