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.
With datatable that is essentially similar to below:
Is there a way to dynamically calculate a rolling weighted average row by row so that when there is a negative value in 'Volume' column it then uses the current inventory value (or previous rows wacog for factor value) to adjust the inventory rather than price so that it would look like below?
For 'RunningNotionalTotal' instead of using the 'Price' column when volume is negative use the previous rows calculated WACOG. This can be done in standard in excel since the rows can be directly referenced with IF statements but using M or DAX I am getting circular references.
Solved! Go to Solution.
Hi @Al_Bundy,
Could you please try something like this?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY47EsAgCAXvQq0RUOyTazDe/xp+CsYJMcWj2WFnVYESUmJkhgD3GOE80IICfyFeKL9Q3N7K2ShnVH+Me+NzbpxIxrJLnCQudIlrNGNxjYbENZqyQmsd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Category = _t, Volume = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category", type text}, {"Volume", Int64.Type}, {"Price", type number}}),
fAddColumns = (r, e) =>
[
RunningTotalVolume = Record.FieldOrDefault(e, "RunningTotalVolume", 0) + r[Volume],
Value = (if r[Volume] < 0 then Record.FieldOrDefault(e, "WACOG", 0) else r[Price]) * r[Volume],
RunningTotalNotional = Record.FieldOrDefault(e, "RunningTotalNotional", 0) + Value,
WACOG = RunningTotalNotional / RunningTotalVolume
]
,
#"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"Category"}, {{"Count", each Table.FromRecords(List.Skip(List.Accumulate(Table.ToRecords(_), {[]}, (a, n)=> a & { n & fAddColumns(n, List.Last(a))} )))}})[Count])
in
#"Grouped Rows"
Kind regards,
John
@lbendlin Thanks for the response. I am going to put something together that will make it clearer what I'm trying to achieve.
Problem I am having with the Index solution is the number of rows in my datatable - anywhere from ~12-15k rows. Surprised its having issues with that many rows. Looking into Buffer and other solutions to get the query to return faster.
Hi @Al_Bundy,
Could you please try something like this?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY47EsAgCAXvQq0RUOyTazDe/xp+CsYJMcWj2WFnVYESUmJkhgD3GOE80IICfyFeKL9Q3N7K2ShnVH+Me+NzbpxIxrJLnCQudIlrNGNxjYbENZqyQmsd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Category = _t, Volume = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category", type text}, {"Volume", Int64.Type}, {"Price", type number}}),
fAddColumns = (r, e) =>
[
RunningTotalVolume = Record.FieldOrDefault(e, "RunningTotalVolume", 0) + r[Volume],
Value = (if r[Volume] < 0 then Record.FieldOrDefault(e, "WACOG", 0) else r[Price]) * r[Volume],
RunningTotalNotional = Record.FieldOrDefault(e, "RunningTotalNotional", 0) + Value,
WACOG = RunningTotalNotional / RunningTotalVolume
]
,
#"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"Category"}, {{"Count", each Table.FromRecords(List.Skip(List.Accumulate(Table.ToRecords(_), {[]}, (a, n)=> a & { n & fAddColumns(n, List.Last(a))} )))}})[Count])
in
#"Grouped Rows"
Kind regards,
John
Brilliant!!! Thank you thank you John.
Now to review what you provided so I can understand what is happening!!
it then uses the current inventory value (or previous rows wacog for factor value) to adjust the inventory rather than price
you lost me on this one. Can you explain again in simpler terms?
Generally in Power Query you add an index column. That will then allow you to reference the "previous row" data points. Using List.Accumulate you can then implement the required logic.
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.