Average cost for last 10 produced items

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...

AilleryO
3 years ago
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