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.
Hi
I am looking for a way to calculate the cost per sales transaction using FIFO method in Power Query, I tried a lot of formulas but none is working.
In my case, I have multiple items and multiple purchasing costs. I attached a sample of the data so it can help better understand the case.
FYI
let
Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
#"Change Type" = Table.TransformColumnTypes(Source,{{"Total Value", type number}, {"value per unit", type number}}),
Custom1 =
Table.Group(
#"Change Type",
"Symbol",
{
"q",
each
let
t = Table.Group( _, "type", { "w", (x)=> x } ),
buy = t{0}[w],
sale = t{1}[w],
f =
(p)=>
List.Accumulate(
Table.ToRows( buy ),
{ 0, 0 },
(x,y)=>
if x{1} + y{1} < p
then { x{0} + y{1} * y{4}, x{1} + y{1} }
else { x{0} + List.Max( { p - x{1}, 0 } ) * y{4}, x{1} + y{1} }
){0},
a =
Table.AddIndexColumn( sale, "i", 1, 1 ),
b =
Table.AddColumn( a, "q", (x)=> f( List.Sum( Table.FirstN( a, x[i] )[Units] ) ) ),
c =
Table.AddColumn( b, "cost", (x)=> if x[i] = 1 then x[q] else x[q] - b[q]{x[i]-2} ),
d =
Table.AddColumn( buy, "cost", (x)=> null ) & Table.RemoveColumns( c, { "i", "q" } )
in
Table.Sort( d, "index" )
}
)[q],
Custom2 =
Table.Combine( Custom1 )
in
Custom2
Hi @ERD
This is the data sample with the expected value as a cost of goods sold column, the calculation for this column was as follows :
for the symbol "100", I first bought 2 pcs for 43.65 each and then we sell 1 pcs so the cost of this 1 pcs is 43.65 after that we bought 1 pcs for 84 then we sell 1 pcs and the cost of this pcs should be 43.65 ( we bought 2 and then sell 1 so we have 1 left), after that, we sell 1 pcs and the cost of this pcs should be 84 ( we finish the first batch of the products that we bought first for 43.65 per item and then we move to the second batch that cost 84 per item),
this case should be repeated for the second item also.
Symbol | Units | type | Total Value | value per unit | index | Cost of goods sold |
100 | 2 | Buy | 87.30 | 43.65 | 1 | |
100 | 1 | Sale | 84.02 | 84.02 | 2 | 43.65 |
100 | 1 | Buy | 84.00 | 84.00 | 3 | |
100 | 1 | Sale | 78.53 | 78.53 | 4 | 43.65 |
100 | 1 | Sale | 92.94 | 92.94 | 5 | 84.00 |
1525 | 16 | Buy | 368.32 | 23.02 | 1 | |
1525 | 100 | Buy | 2,302.00 | 23.02 | 2 | |
1525 | 3 | Buy | 69.06 | 23.02 | 3 | |
1525 | 3 | Buy | 69.06 | 23.02 | 4 | |
1525 | 12 | Sale | 363.49 | 30.29 | 5 | 276.24 |
1525 | 1 | Sale | 40.99 | 40.99 | 6 | 23.02 |
1525 | 4 | Sale | 119.89 | 29.97 | 7 | 92.08 |
1525 | 1 | Sale | 42.50 | 42.50 | 8 | 23.02 |
1525 | 3 | Sale | 105.00 | 35.00 | 9 | 69.06 |
1525 | 1 | Sale | 40.00 | 40.00 | 10 | 23.02 |
1525 | 14 | Buy | 315.64 | 22.55 | 11 | |
1525 | 3 | Sale | 120.00 | 40.00 | 12 | 69.06 |
1525 | 3 | Sale | 150.00 | 50.00 | 13 | 69.06 |
1525 | 3 | Sale | 117.00 | 39.00 | 14 | 69.06 |
1525 | 1 | Sale | 45.34 | 45.34 | 15 | 23.02 |
1525 | 1 | Sale | 41.45 | 41.45 | 16 | 23.02 |
1525 | 10 | Sale | 299.72 | 29.97 | 17 | 230.20 |
1525 | 2 | Sale | 79.91 | 39.96 | 18 | 46.04 |
1525 | 1 | Sale | 39.56 | 39.56 | 19 | 23.02 |
1525 | 1 | Sale | 38.11 | 38.11 | 20 | 23.02 |
1525 | 1 | Sale | 35.00 | 35.00 | 21 | 23.02 |
1525 | 3 | Sale | 83.16 | 27.72 | 22 | 69.06 |
1525 | 45 | Sale | 1,125.00 | 25.00 | 23 | 1,035.90 |
1525 | 1 | Sale | 35.00 | 35.00 | 24 | 23.02 |
1525 | 2 | Sale | 80.09 | 40.04 | 25 | 46.04 |
1525 | 2 | Sale | 80.00 | 40.00 | 26 | 46.04 |
1525 | 2 | Buy | 80.00 | 40.00 | 27 | |
1525 | 2 | Sale | 100.00 | 50.00 | 28 | 46.04 |
1525 | 2 | Sale | 79.99 | 39.99 | 29 | 46.04 |
1525 | 1 | Sale | 39.69 | 39.69 | 30 | 23.02 |
1525 | 2 | Sale | 79.92 | 39.96 | 31 | 46.04 |
1525 | 1 | Sale | 39.99 | 39.99 | 32 | 23.02 |
1525 | 2 | Buy | 80.00 | 40.00 | 33 | |
1525 | 4 | Sale | 164.02 | 41.01 | 34 | 92.08 |
1525 | 2 | Sale | 75.01 | 37.51 | 35 | 46.04 |
1525 | 10 | Buy | 202.43 | 20.24 | 36 | |
1525 | 9 | Sale | 353.96 | 39.33 | 37 | 206.23 |
1525 | 1 | Sale | 40.52 | 40.52 | 38 | 22.55 |
1525 | 3 | Sale | 75.00 | 25.00 | 39 | 67.64 |
1525 | 5 | Sale | 138.60 | 27.72 | 40 | 147.64 |
I'm not sure how to get this in Power Query, but here are some links that might be useful:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @MQudmani ,
As a general advice, please, provide:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1 to 2.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.