Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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?
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
I don't get it yet. You want to use the last three dates?
Where/how are you going to use the measure?
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.
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
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
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
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
Excuse to jump in, but how is qty_remaining (calculated column) being worked out? Is it FIFO logic, and how implemented in DAX?
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
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.
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
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
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
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.
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
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |