Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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
1 ACCEPTED SOLUTION
Memorable Member

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

13 REPLIES 13
Super User

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] )
)
)
Memorable Member

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

Frequent Visitor

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 )

Memorable Member

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

Frequent Visitor

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 👍

Super User

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.

Super User

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

Super User

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:

Frequent Visitor

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

Super User

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:

Frequent Visitor

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

Super User

@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.
Frequent Visitor

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors