cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ktp_99 Frequent Visitor
Frequent Visitor

Calc weighted Average and value of remaining items

Need help with creating measures that calculates total value (total_cost) of remaining item and average unit cost (avg_cost).  These measures are for last remaining qty, so it should be based on latest date (working from latest to old). for below example, takes dec 25 units first, then dec 15, and last remaining from nov 20. 

 

DateQtyPrice     
10-May-18100750.25     
10-Apr-1850800.65     
20-Jul-181000780.25     
5-Aug-18500900.55     
9-Sep-18400760.95     
10-Oct-18200876.25     
25-Oct-18400800.65 Qty Left1800  
6-Nov-18600780.25     
10-Nov-18200900.55 qtypriceTotal Avg
20-Nov-18500760.95 100760.9576095.00 
15-Dec-18700876.25 700876.25613375.00 
25-Dec-181000800.65 1000800.65800650.00 
      1490120.00827.84
        
    measure_avg_cost 827.84 
    measure_total_cost 1490120.00 

 

 

Table contains 3 columns:

     Date -- date units added to inventory

     Qty -- qty added to inventory

     Price -- price for 1 unit

 

     qty_remaining -- calculated measure - current remaining qty 

 

want to create following calculated measures:

     avg_cost -- remaining qty avgerage unit cost

     total_cost -- remaining qty total cost

 

Thank You

16 REPLIES 16
Super User
Super User

Re: Calc weighted Average and value of remaining items

Hi @ktp_99

I think you're going to have to be more concise in explaining what you need. For instance, are the measures for the month of December?

ktp_99 Frequent Visitor
Frequent Visitor

Re: Calc weighted Average and value of remaining items

Hi AIB,

 

These measures are for last remaining qty, so it should be based on latest date (working from latest to old). for given example, takes dec 25 units first, then dec 15, and last remaining from nov 20. so it is not monthly measure, but measure for remaining units (qty_remaining).  

 

I tried using FILTER and SUMX but unsuccessful. 

 

Thanks

Super User
Super User

Re: Calc weighted Average and value of remaining items

@ktp_99

I don't get it yet. You want to use the last three dates?

Where/how are you going to use the measure?

ktp_99 Frequent Visitor
Frequent Visitor

Re: Calc weighted Average and value of remaining items

Basically use as many "last" entries (rows) to make up (sum) qty_remaining.  For this example it requires last 3 rows (1000 + 700 + 100) to add up to qty_remaining = 1800.

 

if qty_remaining = 500, then just use last row (dec 25). 

if qty_remaining = 2500, then use last 5 rows (1000 + 700+ 500 + 200 + 100 from Nov 6).

 

measures provide: avg price and total cost for qty_remaining.

 

Super User
Super User

Re: Calc weighted Average and value of remaining items

@ktp_99

 

Where is the quantity remaining specified? You need to try to explain this properly with all the steps. Otherwise it'll be difficult and time-consuming to come up with a solution

ktp_99 Frequent Visitor
Frequent Visitor

Re: Calc weighted Average and value of remaining items

qty remaining is specified in calculated measure "qty_remaining"

 

Table contains 3 columns:

     Date -- date units added to inventory

     Qty -- qty added to inventory

     Price -- price for 1 unit

 

     qty_remaining -- calculated measure - current remaining qty 

 

want to create following calculated measures:

     avg_cost -- remaining qty avgerage unit cost

     total_cost -- remaining qty total cost

 

Thanks

 

Community Support Team
Community Support Team

Re: Calc weighted Average and value of remaining items

Hi @ktp_99,

 

I'm still not understand your scenario completely.

 

From my understand based on your information, your data sample is bleow. You want to calculate the total which is the sum of qty*price, so how do you return the qty based on the last rows? 

Date                                              Qty         Price

Thursday, May 10, 2018 100 750.25
Tuesday, April 10, 2018 50 800.65
Friday, July 20, 2018 1000 780.25
Sunday, August 5, 2018 500 900.55
Sunday, September 9, 2018 400 760.95
Wednesday, October 10, 2018 200 876.25
Thursday, October 25, 2018 400 800.65
Tuesday, November 6, 2018 600 780.25
Saturday, November 10, 2018 200 900.55
Tuesday, November 20, 2018 500 760.95
Saturday, December 15, 2018 700 876.25
Tuesday, December 25, 2018 1000 800.65

 

I have confused that why the qty for 20-Nov-18 is 100 rather than 500. 

 

In addition, the average of 760.95,876.25 and 800.65 is 812.62 rather than 827.84. 

 

Could you explain it? So that we can understand your logic better and get the solution.

 qty.PNG

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ktp_99 Frequent Visitor
Frequent Visitor

Re: Calc weighted Average and value of remaining items

Cherry,

 

this is based on FIFO (First In First Out), so if there are only 1800 units left (all other qty used up) then 400 units from Nov 20 is already used up and only 100 left from this batch.

 

So 1800 left units are from following batches:

  100 - 20 Nov 18

  700 - 15 Dec 18

1000 - 25 Dec 18

=====

1800 -  Total remaining <--- for this qty want to find Average Cost and Total Cost

 

Hope this clear now.

 

Thanks

Super User
Super User

Re: Calc weighted Average and value of remaining items

@ktp_99

 

Here is the code for the first measure. It's perhaps too verbose but I haven't had time to trim it down. It seems to work though.

 

AverageCost = 
VAR _AuxTable =
    SUMMARIZECOLUMNS (Table1[Date];Table1[Qty];Table1[Price];
        "QtyForCalculation";
        VAR _PreviousCumulative =
            CALCULATE (
                SUM ( Table1[Qty] );
                FILTER ( ALL ( Table1 ); Table1[Date] > VALUES ( Table1[Date] ) )
            )
        VAR _CurrentRowQty = VALUES ( Table1[Qty] )
        RETURN
            IF (_PreviousCumulative + _CurrentRowQty <= [qty_remaining];
                VALUES ( Table1[Qty] );
                IF (_PreviousCumulative < [qty_remaining];
                    [qty_remaining] - _PreviousCumulative
                )
            )
    )
VAR _AvgCost = SUMX ( _AuxTable; [QtyForCalculation] * [Price] )
RETURN _AvgCost

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 62 members 1,086 guests
Please welcome our newest community members: