Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Maln
Frequent Visitor

Average cost for last 10 produced items

‎01-05-2023 05:01 AM

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
Message 1 of 14
827 Views
0
1 ACCEPTED SOLUTION
AilleryO
Memorable Member
Memorable Member

‎01-06-2023 03:30 AM

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

Message 10 of 14
716 Views
13 REPLIES 13
tamerj1
Super User
Super User

‎01-06-2023 06:03 AM

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] )
    )
)
Message 14 of 14
694 Views
0
AilleryO
Memorable Member
Memorable Member

‎01-06-2023 03:30 AM

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
 
Message 10 of 14
717 Views
Maln
Frequent Visitor
In response to AilleryO

‎01-06-2023 04:59 AM

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 )



Message 11 of 14
705 Views
0
AilleryO
Memorable Member
Memorable Member
In response to Maln

‎01-06-2023 07:28 AM

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

 

Message 12 of 14
686 Views
0
Maln
Frequent Visitor
In response to AilleryO

‎01-06-2023 08:59 AM

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 👍

Message 13 of 14
673 Views
FreemanZ
Super User
Super User

‎01-05-2023 05:19 AM

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. 

Message 2 of 14
809 Views
tamerj1
Super User
Super User
In response to FreemanZ

‎01-05-2023 06:07 AM

@FreemanZ 

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

Message 6 of 14
783 Views
0
FreemanZ
Super User
Super User
In response to tamerj1

‎01-05-2023 06:12 AM

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

 

Message 7 of 14
779 Views
0
Maln
Frequent Visitor
In response to FreemanZ

‎01-05-2023 06:15 AM

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

Message 9 of 14
775 Views
0
FreemanZ
Super User
Super User
In response to FreemanZ

‎01-05-2023 06:15 AM

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

 

Message 8 of 14
776 Views
0
Maln
Frequent Visitor
In response to FreemanZ

‎01-05-2023 05:34 AM

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

 

Message 5 of 14
799 Views
0
Mahesh0016
Super User
Super User
In response to FreemanZ

‎01-05-2023 05:23 AM

@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.
Message 3 of 14
805 Views
Maln
Frequent Visitor
In response to Mahesh0016

‎01-05-2023 05:37 AM

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

Message 4 of 14
794 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All
Top Solution Authors
View All