Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
Solved! Go to Solution.
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] )
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 )
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] )
)
)
OR
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] )
)
)
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] )
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 )
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:
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...
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 👍
hi @Maln
try like:
if there is issue, please consider providing some sample data and @me.
Just replace ItemLedger[Date] with ItemLedger[Serial No.] or ItemLedger[Entry No.]
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:
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...
wait @Maln , it might still need to reference the posting date or Entry No., like this:
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 |
@Maln
Last10 Output Cost AVG per PCS PY =
Hi, thanks for the help @Mahesh0016, but the measure doesn't work. I have added an example in the previous reply.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
20 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
25 | |
24 | |
22 |