Dax Measure: Average per last x items per group

[wlknsn]

Hi,

I'm looking for a measure to calculate the average sales of the last 3 items per product/group. Like below.

Product	OrderNumber	Sales	AveragePerLast3Items
A	1	3
A	2	6
A	3	7	5,333333333
A	4	4	5,666666667
B	7	4
B	8	7
B	12	8	6,333333333
B	14	5	6,666666667

Many thanks!

[v-juanli-msft · Accepted solution]

Hi @wlknsn

Create a calculated column

rank = RANKX(FILTER(ALL(Sheet10),[Product]=EARLIER(Sheet10[Product])),[OrderNumber],,ASC)

Then create a measure

Measure =
VAR selected =
    SELECTEDVALUE ( Sheet10[OrderNumber] )
VAR filtered =
    TOPN (
        3,
        FILTER (
            ALL ( Sheet10 ),
            [OrderNumber] <= selected
                && [Product] = SELECTEDVALUE ( Sheet10[Product] )
        ),
        [OrderNumber], DESC
    )
RETURN
    AVERAGEX ( filtered, [Sales] )

Finaly with this calculated column

Average per last x items per group = IF([rank]>=3,[Measure],BLANK())

Best Regards

Maggie

[wlknsn]

Anyone???