Calc weighted Average and value of remaining items

[ktp_99]

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

[AlB]

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]

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

[AlB]

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

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.

[AlB]

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

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

[v-piga-msft]

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

[ktp_99]

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

[pers]

Excuse to jump in, but how is qty_remaining (calculated column) being worked out? Is it FIFO logic, and how implemented in DAX?

[AlB]

@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

[AlB]

@ktp_99

In both cases, Table1 is the name of your table. Be careful with your [qty_remaining] measure. Depending on its code you might need to modify it slightly to avoid issues with the filter context provided by SUMMARIZECOLUMNS. If [qty_remaining] does not depend on Table1 at all, I believe it should be fine.

[ktp_99]

Thank you AIB

qty_remaining is not related to Table1 data and it is in another table

this code seems to be working, but when I added one more row in table1 

Date            Qty    Price

25-Dec-18   500   850.25

AveragePrice is still working, but AverageCost (which is total cost) is not working when qty is less than total 25-Dec-18 qty.  For example, if I set qty_remaining =1 or 500, or 1000.

I wonder if code breaks when there are multiple entries on same Date. 

Thanks

[v-piga-msft]

Hi @ktp_99,

Have you solved your problem?

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[ktp_99]

AIB provided great help.  Thanks AIB.

However my end solution still not working. AIB solution works if I load data from Excel. But in my case table (columns -  Date, Qty, Price) is created using power query using Table.SelectColumns and when I create measure in this table -  not seem to provide correct answer (Average Price).  Also table contains multiple enteries on same date - I've tried to summerize using Table.Group.   

let
Source = Table.SelectColumns(PO_RCV,{"RCV_DATE","RCV_QTY","UNIT_PRICE"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RCV_DATE", type date},{"RCV_QTY", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"RCV_DATE", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([RCV_DATE] <> null)),
#"Grouped Rows1" = Table.Group(#"Filtered Rows", {"RCV_DATE"}, {{"Qty", each List.Sum([RCV_QTY]), type number}, {"Price", each List.Average([UNIT_PRICE]), type number}}),
#"Sorted Rows1" = Table.Sort(#"Grouped Rows1",{{"RCV_DATE", Order.Ascending}}),
in

Thanks

[AlB]

@ktp_99

Yup, the code pretty much assumes there is only one row per date. You can update the measures to take that into account or, probably easier, pre-process your table to have only one entry per date. 

[AlB]

@ktp_99

And the second one. As you can see almost all the code is the same as that of the first measure:

AveragePrice = 
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] )
VAR _AvgPrice = DIVIDE ( _AvgCost; SUMX ( _AuxTable; [QtyForCalculation] ) )
RETURN
    _AvgPrice