Average on selected Value

[arvindarvind24]

Hi,

  Need to calcuate Average on selected Value.

Product	Style No	Sale Qty	Avg Sales Qty	Sales Qty/ Avg Sales Qty
CD SET	1	30	6.26	4.79
CD SET	2	28	6.26	4.47
CD SET	3	14	6.26	2.24
CD SET	4	13	6.26	2.08
CD SET	5	11	6.26	1.76
CD SET	6	9	6.26	1.44
CD SET	7	8	6.26	1.28
CD SET	8	7	6.26	1.12
CD SET	9	7	6.26	1.12
CD SET	10	6	6.26	0.96
CD SET	11	6	6.26	0.96
CD SET	12	5	6.26	0.80
CD SET	13	5	6.26	0.80
CD SET	14	4	6.26	0.64
CD SET	15	4	6.26	0.64
CD SET	16	4	6.26	0.64
CD SET	17	4	6.26	0.64
CD SET	18	4	6.26	0.64
CD SET	19	4	6.26	0.64
CD SET	20	3	6.26	0.48
CD SET	21	3	6.26	0.48
CD SET	22	3	6.26	0.48
CD SET	23	3	6.26	0.48
CD SET	24	3	6.26	0.48
CD SET	25	3	6.26	0.48
CD SET	26	3	6.26	0.48
CD SET	27	3	6.26	0.48
CD SET	28	3	6.26	0.48
CD SET	29	3	6.26	0.48
CD SET	30	2	6.26	0.32
CD SET	31	2	6.26	0.32
CD SET	32	2	6.26	0.32
CD SET	33	2	6.26	0.32
CD SET	34	2	6.26	0.32

@amitchauhan @bvanderwatt @ray_ux @tamerj1 @SpartaBI @v-chenyue-msft  @mangaus1111

@v-jianhe-msft 

[arvindarvind24]

Hi All,
  After using the ALLEXCEPT the value is not getting.

Result Wanted:

[arvindarvind24]

Hi @tamerj1 ,

Based on product i need the average.

Product	Size	Style No	Colour	Sale Qty	Avg Sales Qty	Sales Qty/ Avg Sales Qty
Product01	S	1	Red	30	12.63	2.38
Product01	M	2	Black	28	12.63	2.22
Product01	XL	3	Green	14	12.63	1.11
Product01	XXL	4	Red	13	12.63	1.03
Product01	S	5	Black	11	12.63	0.87
Product01	M	6	Green	9	12.63	0.71
Product01	XL	7	Red	8	12.63	0.63
Product01	XXL	8	Black	7	12.63	0.55
Product01	S	9	Green	7	12.63	0.55
Product01	M	10	Red	6	12.63	0.48
Product01	XL	11	Black	6	12.63	0.48
Product02	S	12	Green	5	3.83	1.31
Product02	M	13	Red	5	3.83	1.31
Product02	XL	14	Black	4	3.83	1.04
Product02	XXL	15	Green	4	3.83	1.04
Product02	S	16	Red	4	3.83	1.04
Product02	M	17	Black	4	3.83	1.04
Product02	XL	18	Green	4	3.83	1.04
Product02	XXL	19	Red	4	3.83	1.04
Product02	S	20	Black	3	3.83	0.78
Product02	M	21	Green	3	3.83	0.78
Product02	XL	22	Red	3	3.83	0.78
Product02	S	23	Black	3	3.83	0.78
Product03	XL	24	Green	3	2.54	1.18
Product03	XXL	25	Red	3	2.54	1.18
Product03	S	26	Black	3	2.54	1.18
Product03	M	27	Green	3	2.54	1.18
Product03	XL	28	Red	3	2.54	1.18
Product03	XXL	29	Black	3	2.54	1.18
Product03	S	30	Green	2	2.54	0.79
Product03	M	31	Red	2	2.54	0.79
Product03	XL	32	Black	2	2.54	0.79
Product03	M	33	Green	2	2.54	0.79
Product03	M	34	Red	2	2.54	0.79

Product	Sales Qty	Avg Value
Product01	139	12.63
Product02	46	3.83
Product03	28	2.54

[tamerj1]

Hi @arvindarvind24 
Please elaborate more on the expected results

[arvindarvind24]

FYI.

Table1	Table1	Table1	Table1	Table2	Based on Product I need the Avg (Result Required)
Product	Size	Style No	Colour	Sale Qty	Avg Sales Qty
Product01	S	1	Red	30	12.63
Product01	M	2	Black	28	12.63
Product01	XL	3	Green	14	12.63
Product01	XXL	4	Red	13	12.63
Product01	S	5	Black	11	12.63
Product01	M	6	Green	9	12.63
Product01	XL	7	Red	8	12.63
Product01	XXL	8	Black	7	12.63
Product01	S	9	Green	7	12.63
Product01	M	10	Red	6	12.63
Product01	XL	11	Black	6	12.63
Product02	S	12	Green	5	3.83
Product02	M	13	Red	5	3.83
Product02	XL	14	Black	4	3.83
Product02	XXL	15	Green	4	3.83
Product02	S	16	Red	4	3.83
Product02	M	17	Black	4	3.83
Product02	XL	18	Green	4	3.83
Product02	XXL	19	Red	4	3.83
Product02	S	20	Black	3	3.83
Product02	M	21	Green	3	3.83
Product02	XL	22	Red	3	3.83
Product02	S	23	Black	3	3.83
Product03	XL	24	Green	3	2.54
Product03	XXL	25	Red	3	2.54
Product03	S	26	Black	3	2.54
Product03	M	27	Green	3	2.54
Product03	XL	28	Red	3	2.54
Product03	XXL	29	Black	3	2.54
Product03	S	30	Green	2	2.54
Product03	M	31	Red	2	2.54
Product03	XL	32	Black	2	2.54
Product03	M	33	Green	2	2.54
Product03	M	34	Red	2	2.54

[tamerj1]

@arvindarvind24 
How are table1 and Table2 connected? Are looking for a measure or a calculated column?

[arvindarvind24]

@tamerj1 There are connected through the icode relationship and am looking for measure.

[tamerj1]

@arvindarvind24 
However, please try

Avg Sales Qty =
CALCULATE (
    AVERAGE ( Table2[Sales Qty] ),
    ALLEXCEPT ( Table1, Table1[Produvt] )
)

[tamerj1]

@arvindarvind24 
What kind of relationship? Which filters which?

[arvindarvind24]

@tamerj1 One to Many relationship with single filter of product.