Forum Discussion

Maln's avatar
Maln
Frequent Visitor
3 years ago
Solved

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
  • AilleryO's avatar
    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
     

13 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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. 

    • Mahesh0016's avatar
      Mahesh0016
      Icon for Super User rankSuper User
      3 years ago

      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.
      • Maln's avatar
        Maln
        Frequent Visitor
        3 years ago

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

    • Maln's avatar
      Maln
      Frequent Visitor
      3 years ago

      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 TypePosting DateLocation CodeDocument No.Item No.Serial No.QuantityInvoiced QuantityCost Amount (Actual)Entry No.
      Output6.10.2022SERV_WMSRO0086686196002200033711123,001251750
      Output6.10.2022SERV_WMSRO0084846196002200033011123,001251714
      Output23.08.2022SERV_WMSRO008482619600220003281189,001212523
      Output18.08.2022SERV_WMSRO0084816196002200032711140,001208554
      Output18.08.2022SERV_WMSRO0084806196002200032611113,001208519
      Output16.08.2022SERV_WMSRO0083046196002200032311123,001206226
      Output11.08.2022SERV_WMSRO0083036196002200032211123,001204241
      Output8.08.2022SERV_WMSRO0083026196002200032111123,001200803
      Output2.08.2022SERV_WMSRO0083006196002200031911123,001196397
      Output2.08.2022SERV_WMSRO0083016196002200032011123,001196357
      Output2.08.2022SERV_WMSRO0082996196002200031811123,001196318
      Output2.08.2022SERV_WMSRO0082986196002200031711123,001196279
      Output29.07.2022SERV_WMSRO0083056196002200032411123,001193791
      Output29.07.2022SERV_WMSRO0083066196002200032511123,001193676
      Output29.07.2022SERV_WMSRO0082976196002200031611123,001193637
      Output29.07.2022SERV_WMSRO0082796196002200030711123,001193596
      Output26.07.2022SERV_WMSRO0082806196002200030811123,001191379
      Output26.07.2022SERV_WMSRO0082786196002200030611123,001191340
      Output26.07.2022SERV_WMSRO0082776196002200030511123,001191302
      Output26.07.2022SERV_WMSRO0082766196002200030411123,001191259
      Output22.07.2022SERV_WMSRO0082556196002200060511123,001188403
      Output22.07.2022SERV_WMSRO0082546196002200060411123,001188360
      Output21.07.2022SERV_WMSRO0082536196002200060311123,001188020
      Output21.07.2022SERV_WMSRO0082526196002200060211123,001187981
      Output19.07.2022SERV_WMSRO0082516196002200060111123,001185745
      Output19.07.2022SERV_WMSRO0082506196002200060011123,001185707
      Output19.07.2022SERV_WMSRO0082496196002200059911123,001185664
      Output19.07.2022SERV_WMSRO0082486196002200059811123,001185626
      Output15.07.2022SERV_WMSRO0082476196002200059711123,001184118
      Output15.07.2022SERV_WMSRO0082466196002200059611123,001184080
      Output15.07.2022SERV_WMSRO0082456196002200059511123,001183271

       

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      FreemanZ 

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

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        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:

         

  • AilleryO's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    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
     
    • Maln's avatar
      Maln
      Frequent Visitor
      3 years ago

      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 )



      • AilleryO's avatar
        AilleryO
        Icon for Memorable Member rankMemorable Member
        3 years ago

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

         

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

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