topic Re: Average cost for last 10 produced items in DAX Commands and Tips

Average cost for last 10 produced items

Maln — Thu, 05 Jan 2023 13:02:32 GMT

Hi,

I have a problem creating a measure to calculate the average cost for the last 10 produced items.

I've had a similar measure for calculating avg price for a previous year and there wasn't a problem, but cannot figure out how to do it for the last 10...

This is a measure for the previous year:

Output Cost AVG per PCS PY =

CALCULATE(

(SUM(ItemLedger[Cost Amount (Actual)]) / SUM(ItemLedger[Invoiced Quantity])),

ItemLedger[Entry Type] = "Output",

dim_Dates[Year] = YEAR(TODAY())-1

)

Thanks for the help

BR, Klemen

Re: Average cost for last 10 produced items

FreemanZ — Thu, 05 Jan 2023 13:19:13 GMT

hi Maln

try like:

Last10 Output Cost AVG per PCS PY =

CALCULATE(

(SUM(ItemLedger[Cost Amount (Actual)]) / SUM(ItemLedger[Invoiced Quantity])),

ItemLedger[Entry Type] = "Output",

dim_Dates[Year] = YEAR(TODAY())-1,

TOPN(10, ItemLedger, ItemLedger[Date])

)

if there is issue, please consider providing some sample data and @me.

Re: Average cost for last 10 produced items

Mahesh0016 — Thu, 05 Jan 2023 13:23:04 GMT

Maln
Last10 Output Cost AVG per PCS PY =

CALCULATE(

(SUM(ItemLedger[Cost Amount (Actual)]) / SUM(ItemLedger[Invoiced Quantity])),

ItemLedger[Entry Type] = "Output",

dim_Dates[Year] = YEAR(TODAY())-1,

TOPN(10, ItemLedger, ItemLedger[Date],desc)

)

Maln If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.

Re: Average cost for last 10 produced items

Maln — Thu, 05 Jan 2023 13:48:15 GMT

Thanks for the replay FreemanZ, but the measure doesn't work, it shows blank.

I am sending a table with an example. This is filtered to one item and only output

Entry Type	Posting Date	Location Code	Document No.	Item No.	Serial No.	Quantity	Invoiced Quantity	Cost Amount (Actual)	Entry No.
Output	6.10.2022	SERV_WMS	RO008668	619600	22000337	1	1	123,00	1251750
Output	6.10.2022	SERV_WMS	RO008484	619600	22000330	1	1	123,00	1251714
Output	23.08.2022	SERV_WMS	RO008482	619600	22000328	1	1	89,00	1212523
Output	18.08.2022	SERV_WMS	RO008481	619600	22000327	1	1	140,00	1208554
Output	18.08.2022	SERV_WMS	RO008480	619600	22000326	1	1	113,00	1208519
Output	16.08.2022	SERV_WMS	RO008304	619600	22000323	1	1	123,00	1206226
Output	11.08.2022	SERV_WMS	RO008303	619600	22000322	1	1	123,00	1204241
Output	8.08.2022	SERV_WMS	RO008302	619600	22000321	1	1	123,00	1200803
Output	2.08.2022	SERV_WMS	RO008300	619600	22000319	1	1	123,00	1196397
Output	2.08.2022	SERV_WMS	RO008301	619600	22000320	1	1	123,00	1196357
Output	2.08.2022	SERV_WMS	RO008299	619600	22000318	1	1	123,00	1196318
Output	2.08.2022	SERV_WMS	RO008298	619600	22000317	1	1	123,00	1196279
Output	29.07.2022	SERV_WMS	RO008305	619600	22000324	1	1	123,00	1193791
Output	29.07.2022	SERV_WMS	RO008306	619600	22000325	1	1	123,00	1193676
Output	29.07.2022	SERV_WMS	RO008297	619600	22000316	1	1	123,00	1193637
Output	29.07.2022	SERV_WMS	RO008279	619600	22000307	1	1	123,00	1193596
Output	26.07.2022	SERV_WMS	RO008280	619600	22000308	1	1	123,00	1191379
Output	26.07.2022	SERV_WMS	RO008278	619600	22000306	1	1	123,00	1191340
Output	26.07.2022	SERV_WMS	RO008277	619600	22000305	1	1	123,00	1191302
Output	26.07.2022	SERV_WMS	RO008276	619600	22000304	1	1	123,00	1191259
Output	22.07.2022	SERV_WMS	RO008255	619600	22000605	1	1	123,00	1188403
Output	22.07.2022	SERV_WMS	RO008254	619600	22000604	1	1	123,00	1188360
Output	21.07.2022	SERV_WMS	RO008253	619600	22000603	1	1	123,00	1188020
Output	21.07.2022	SERV_WMS	RO008252	619600	22000602	1	1	123,00	1187981
Output	19.07.2022	SERV_WMS	RO008251	619600	22000601	1	1	123,00	1185745
Output	19.07.2022	SERV_WMS	RO008250	619600	22000600	1	1	123,00	1185707
Output	19.07.2022	SERV_WMS	RO008249	619600	22000599	1	1	123,00	1185664
Output	19.07.2022	SERV_WMS	RO008248	619600	22000598	1	1	123,00	1185626
Output	15.07.2022	SERV_WMS	RO008247	619600	22000597	1	1	123,00	1184118
Output	15.07.2022	SERV_WMS	RO008246	619600	22000596	1	1	123,00	1184080
Output	15.07.2022	SERV_WMS	RO008245	619600	22000595	1	1	123,00	1183271

Re: Average cost for last 10 produced items

Maln — Thu, 05 Jan 2023 13:37:50 GMT

Hi, thanks for the help Mahesh0016, but the measure doesn't work. I have added an example in the previous reply.

Re: Average cost for last 10 produced items

tamerj1 — Thu, 05 Jan 2023 14:07:26 GMT

FreemanZ

Just replace ItemLedger[Date] with ItemLedger[Serial No.] or ItemLedger[Entry No.]

Re: Average cost for last 10 produced items

FreemanZ — Thu, 05 Jan 2023 14:12:14 GMT

Thank you tamerj1

hi Maln

just as tamerj1 mentioned, just try this:

Last10 Output Cost AVG per PCS PY =
CALCULATE(
(SUM(ItemLedger[Cost Amount (Actual)]) / SUM(ItemLedger[Invoiced Quantity])),
ItemLedger[Entry Type] = "Output",
dim_Dates[Year] = YEAR(TODAY())-1,
TOPN(10, ItemLedger, ItemLedger[Serial No.])
)

i tried and it worked:

Re: Average cost for last 10 produced items

FreemanZ — Thu, 05 Jan 2023 14:16:57 GMT

wait Maln , it might still need to reference the posting date or Entry No., like this:

Last10 Output Cost AVG per PCS PY =

CALCULATE(

(SUM(ItemLedger[Cost Amount (Actual)]) / SUM(ItemLedger[Invoiced Quantity])),

ItemLedger[Entry Type] = "Output",

dim_Dates[Year] = YEAR(TODAY())-1,

TOPN(10, ItemLedger, ItemLedger[Posting Date])

)

tried and worked like this:

Re: Average cost for last 10 produced items

Maln — Thu, 05 Jan 2023 14:15:48 GMT

I tried before with the serial no, it gives the correct price, but the thing is serial no doesn't always go in order entry no. would be the best choice but it isn't working as it should...

Re: Average cost for last 10 produced items

AilleryO — Fri, 06 Jan 2023 11:30:45 GMT

Hi Maln,

Can you tell us more what you expect when saying last 10.

Option 1 : If it's by production date, you might have more than one row on one day, so does it mean last 10 dates (refer to Mahesh0016 's formula). Knowing that it might include more than 10 production (rows).

Avg Cost 10 last production = 
VAR _Lst_10_LastProd = TOPN( 10 , 'Datas table' , 'Datas table'[Date production] , DESC )
RETURN
AVERAGEX( _Lst_10_LastProd, 'Datas table'[Production Cost] )

Option 2 you want the 10 last production/rows (whatever production date), then you should work with Entry No., or Serial No ? but you might have less than 10 different dates in your sample. If this is your goal then just replace in TOPN function Date by Entry No..

In both cases it might be a better idea to use DIVIDE instead of / (in case you have a null or 0).

Avg Cost 10 last production = 
VAR _Lst_10_LastProd = TOPN( 10 , 'Datas table' , 'Datas table'[Entry No] , DESC )
RETURN
AVERAGEX( _Lst_10_DernProd, 'Datas table'[Production Cost] )

All together, should be something like, if we use Serial No, and a variable to ceta the list before CALCULATE modify the context :

Last10 Output Cost AVG per PCS PY =
VAR _LstTop10 = TOPN(10, ItemLedger, ItemLedger[Serial No])
CALCULATE(
   DIVIDE (SUM(ItemLedger[Cost Amount (Actual)]) , SUM(ItemLedger[Invoiced Quantity])),
             ItemLedger[Entry Type] = "Output",
            dim_Dates[Year] = YEAR(TODAY())-1,
             _LstTop10 )

So maybe when defining your variable you might need to add some filters (last year, output).

Hope this helps and do not hesitate to give us exact expected results to be sure we understand your needs.

Have a nice day

Re: Average cost for last 10 produced items

Maln — Fri, 06 Jan 2023 12:59:02 GMT

Hi AilleryO ,

Yes, I need last average cost for the last 10 produced items (no matter the year or if they were all created in on day or in 10 diferetnt dates)

I tried with your measure but i am not getting the right avg cost. I think Entry No. is the right data for that, but not shure why it gives me blank value...

I used this measure:

Output Cost AVG per PCS last 10 =

VAR _LstTop10 = TOPN(10, ItemLedger, ItemLedger[Entry No_])

Return

CALCULATE(

DIVIDE (SUM(ItemLedger[Cost Amount (Actual)]) , SUM(ItemLedger[Invoiced Quantity])),

ItemLedger[Entry Type] = "Output",

_LstTop10 )

Re: Average cost for last 10 produced items

tamerj1 — Fri, 06 Jan 2023 14:03:20 GMT

Hi Maln
Please try

Avg Cost 10 last production = SUMX ( VALUES ( ItemLedger[Item No.] ), AVERAGEX ( TOPN ( 10, CALCULATETABLE ( ItemLedger ), ItemLedger[Entry No_] ), DIVIDE ( ItemLedger[Cost Amount (Actual)], ItemLedger[Invoiced Quantity] ) ) )

Avg Cost 10 last production = AVERAGEX ( VALUES ( ItemLedger[Item No.] ), AVERAGEX ( TOPN ( 10, CALCULATETABLE ( ItemLedger ), ItemLedger[Entry No_] ), DIVIDE ( ItemLedger[Cost Amount (Actual)], ItemLedger[Invoiced Quantity] ) ) )

Re: Average cost for last 10 produced items

AilleryO — Fri, 06 Jan 2023 15:28:04 GMT

Hi,

Can you tell us more what is not correct, and what you expect, so that we can adjust.

Another solution could be to create an index column to choose the 10 lats rows...

But we need more information from you, to be able to help...

Re: Average cost for last 10 produced items

Maln — Fri, 06 Jan 2023 16:59:30 GMT

Hi,

I have created a test report, where DAX is OK. I think there might be a problem in a data warehouse, I assume that entry no is not stored as number but text...

I have used your measure and it works on test report.

Last10 Output Cost AVG per PCS PY = VAR _LstTop10 = TOPN(10, ItemLedger, ItemLedger[Entry No_]) Return CALCULATE( DIVIDE (SUM(ItemLedger[Cost Amount (Actual)]) , SUM(ItemLedger[Invoiced Quantity])), ItemLedger[Entry Type] = "Output", _LstTop10 )

Thanks for the help 👍