Last 5 Average Measure

[NishPatel]

I have below data. And New Part No is added (Incremental and Unique) with new cost and Part type. I am looking for a measure that will give me average cost of last 5 part no. by part type.

Part No.	Cost	Part Type
10001	16	Part A
10002	19	Part A
10003	24	Part A
10004	25	Part A
10005	22	Part A
10006	27	Part A
10007	26	Part A
10008	22	Part A
10009	29	Part A
10010	17	Part A
10001	105	Part B
10002	80	Part B
10003	114	Part B
10004	112	Part B
10005	95	Part B
10006	160	Part B
10007	89	Part B
10008	58	Part B
10009	51	Part B
10010	88	Part B
10001	11056	Part C
10002	3992	Part C
10003	3718	Part C
10004	4337	Part C
10005	4078	Part C
10006	1174	Part C
10007	9540	Part C
10008	4646	Part C
10009	5113	Part C
10010	11466	Part C

Measure that I am looking for will give me below result.

Part Type	Last 5 Avg
Part A	24.20
Part B	89.20
Part C	6387.80

Thank You in Advance

Nish

[v-jayw-msft · Accepted solution]

Hi @NishPatel ,

Please check the formula:

Last_5_avg =
CALCULATE (
    AVERAGE ( 'Table'[Cost] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Part Type] = SELECTEDVALUE ( 'Table'[Part Type] )
            && 'Table'[Part No.] <= MAX ( 'Table'[Part No.] )
            && 'Table'[Part No.]
                > MAX ( 'Table'[Part No.] ) - 5
    )
)

Best Regards,

Jay

[amitchandak]

@NishPatel , Try like

Create a rank column first
Rank = rankx(filter(Table, [Part Type] = earlier([Part Type])), [Part No.],,desc, dense)

then create a measure
AvergaeX(Filter(Table, Table[Rank]<=5), Table[Cost])

[NishPatel]

Rank is working perfectly. However, It's giving me 5 different averages by part no and not by part type. I am looking for an average cost of last 5 part nos for each part type. From my table above, Last 5 Actuals for Part A are 27,26,22,29 and 17 so Average should be 24.2. But I am getting 5 different Average's for last 5 Part nos.