DAX Formula help for Average Cost of an item

[Anonymous]

Hi there,

Kindly help to have DAX formula for the below table

each item has different amount either issued or received (+1 or -1) and i need to have average cost of an each item irrespective of date

Date	Item No.	Item Qty	Description	Amount
31/03/2019	In house	1	In houDe	6,076,135.41
31/01/2019	In house	1	In houDe	5,532,795.11
31/12/2018	In house	1	In houDe	4,959,654.48
31/10/2018	In house	1	In houDe	4,909,921.49
30/11/2018	In house	1	In houDe	4,900,504.26
30/09/2018	In house	1	In houDe	4,253,778.16
28/02/2019	In house	1	In houDe	3,767,478.03
31/07/2018	In house	1	In houDe	3,519,880.51
30/06/2018	In house	1	In houDe	2,925,793.64
24/07/2018	161W2100-2	-1	MOMPONENT ADDY - MLG	-                2,425,042.25
01/07/2018	161W2100-2	1	MOMPONENT ADDY - MLG	2,210,740.01
24/07/2018	161W2100-2	-1	MOMPONENT ADDY - MLG	-                2,210,726.43
24/07/2018	162W1100-14	-1	DHOMKDTKUTADDY	-                2,210,726.43
26/02/2019	B445000-038	-1	TOP MOLLEMTOK	-                1,964,821.76
31/05/2018	In house	1	In houDe	1,753,442.25
31/10/2018	201585003-020	-1	LEG AND DKEDDINGD-MLG	-                1,605,675.73
30/04/2018	In house	1	In houDe	1,452,260.74
21/05/2018	201585003-020	1	LEG AND DKEDDINGD-MLG	1,279,996.79
31/05/2018	201585003-020	-1	LEG AND DKEDDINGD-MLG	-                1,278,030.38
01/03/2019	47172-500	1	AMTUATOK - THD	1,274,525.00
30/09/2018	In house	1	In houDe	1,110,838.92
23/10/2018	683L250G03	1	KIT -FAN KEVEKDEK PODN 2 KH	1,108,387.97
16/07/2018	F71AR010000200	1	AIK INLET NODE MOWL	1,087,393.38
26/10/2018	7010101H09	1	MAM - MABIN AIK MOMPKEDDOK	866,205.24
20/10/2018	7010101H09	1	MAM - MABIN AIK MOMPKEDDOK	861,315.77
31/08/2018	In house	1	In houDe	858,814.37
03/06/2018	7010101H09	1	MAM - MABIN AIK MOMPKEDDOK	833,621.72
01/06/2018	7010101H09	1	MAM - MABIN AIK MOMPKEDDOK	823,105.72
03/06/2018	7010101H09	1	MAM - MABIN AIK MOMPKEDDOK	812,185.05
08/08/2018	7010101H09	1	MAM - MABIN AIK MOMPKEDDOK	809,483.84
07/11/2018	7010101H09	1	MAM - MABIN AIK MOMPKEDDOK	803,936.94
01/12/2018	161W2130-1	1	TKUMK MOMPONENT ADDEMBLY	792,355.77
01/12/2018	161W4200-10	1	DTKUT ADDY - DKAG	792,355.77
01/02/2019	161W2100-1	1	MOMPONENT ADDY - MLG	792,355.77
01/02/2019	161W2100-2	1	MOMPONENT ADDY - MLG	792,355.77
30/04/2018	In house	1	In houDe	787,462.24
01/08/2018	7010101H09	1	MAM - MABIN AIK MOMPKEDDOK	783,795.15
28/09/2018	7010101H09	1	MAM - MABIN AIK MOMPKEDDOK	778,905.67
31/12/2018	7010101H09	1	MAM - MABIN AIK MOMPKEDDOK	778,905.67
18/09/2018	752168B	1	IDG-INTEGKATED DKIVE GENEKATOK	737,077.33

Thanks in advance

Nizam

[AlB · Accepted solution]

Hi @Anonymous 

How would you want to take the quantity into consideration to calculate the average? Can you provide an example with one of the items with the expected result? It's not quite clear.

You can try something like this but I'm not sure it's what you're after:

1. Place Table1[Item No.] in a table visual

2. Create this measure and place it on the visual

Measure  = AVERAGE(Table1[Amount])

[Anonymous]

Hi,

Thanks for the reply

and let me elaborate 

Each item (which is repeating in the table) has different Amount 

i need average of cost (including positive and negative values) of each item through DAX formula 

Thanks in advance for your time 

Regards

Nizam

[AlB]

@Anonymous 

I'm afraid it's still unclear. Why don't you give me an example with one of the items, showing how it would be calculated and the result . I don't understand what you mean by  "(including positive and negative values)"

[Anonymous]

Hi

Please find below expected result from above table through DAX formula

Item	Average of Amount
161W2100-1	792355.77
161W2100-2	-408168.23
161W2130-1	792355.77
161W4200-10	792355.77
162W1100-14	-2210726.43
201585003-020	-534569.77
47172-500	1274525.00
683L250G03	1108387.97
7010101H09	807235.87
752168B	721710.80
B445000-038	-1964821.76
F71AR010000200	1087393.38
In house	3343482.83

Thanks and regards

Nizam

[AlB]

The solution I provided earlier should produce exactly that result. Doesn't it?

[Anonymous]

Yes, it is 

Thanks  a lot for the solution and your time

Regards

Nizam