Average of distinct item

[YJ_SG]

I have a table as below.

Wana to do a average of Unit Price of each item. For example, WK001 item A is 8, B is 10 and C is 8.

Average of unit price should be (8+10+8)/3 = 8.67.

Table:
Load * inline[
Week,Item,Qty,Location,Unit Price
WK001,A,100,S1,8
WK001,A,150,S2,8
WK001,A,200,S3,8
WK001,B,300,S1,10
WK001,B,350,S2,10
WK001,C,400,S2,8
WK001,C,450,S3,8
WK002,A,50,S1,9
WK002,A,80,S2,9
WK002,B,100,S1,12
WK002,B,300,S2,12
WK002,B,500,S3,12
WK002,C,500,S2,10
WK002,C,600,S3,10
];

[Taoufiq_Zarra]

@YJ_SG  in script for example

Table:
Load * inline [
Week,Item,Qty,Location,Unit Price
WK001,A,100,S1,8
WK001,A,150,S2,8
WK001,A,200,S3,8
WK001,B,300,S1,10
WK001,B,350,S2,10
WK001,C,400,S2,8
WK001,C,450,S3,8
WK002,A,50,S1,9
WK002,A,80,S2,9
WK002,B,100,S1,12
WK002,B,300,S2,12
WK002,B,500,S3,12
WK002,C,500,S2,10
WK002,C,600,S3,10
];

left join

load Week,Item, avg([Unit Price]) as AverageItem resident Table group by Week,Item;

output:

[YJ_SG · Accepted solution]

Avg(Aggr([Unit Price],Item,Week))

It works for me.