Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.