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

Get 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

Reply
Maln
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
AilleryO
Memorable Member
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
 

View solution in original post

13 REPLIES 13
tamerj1
Super User
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] )
    )
)
AilleryO
Memorable Member
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
 
Maln
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 )



AilleryO
Memorable Member
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...

 

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

 

Maln_0-1673024175979.png

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 👍

FreemanZ
Super User
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. 

@FreemanZ 

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:

FreemanZ_0-1672927865766.png

 

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

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:
FreemanZ_0-1672928060646.png

 

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

 

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors