cancel
Showing results for
Did you mean:
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.

 Date Qty Price 10-May-18 100 750.25 10-Apr-18 50 800.65 20-Jul-18 1000 780.25 5-Aug-18 500 900.55 9-Sep-18 400 760.95 10-Oct-18 200 876.25 25-Oct-18 400 800.65 Qty Left 1800 6-Nov-18 600 780.25 10-Nov-18 200 900.55 qty price Total Avg 20-Nov-18 500 760.95 100 760.95 76095.00 15-Dec-18 700 876.25 700 876.25 613375.00 25-Dec-18 1000 800.65 1000 800.65 800650.00 1490120.00 827.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

## 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?

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

## 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?

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

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

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

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

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

## 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```

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 386 members 3,382 guests
Recent signins: