Forum Discussion
Excel scrap cost formula as Power Bi Measure
Hi
I wonder if I could have some help in trying to recreate the following excel Material Scrap formula as a Power Bi Measure?
Thanks
Rob
Hi , Rob_Jackson
As i test for a very long long time in my side , i realize it in Power Query !~
If this can help you , you can try it in the "Advanced Editor":
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcrBDcAwCAPAXXhHFYFAyCwo+69Rl195INlnMmnSqONHQtgm3ZEkAKnha4qk9cImrlK4AKujAayjA7zjBuyOAYiOB3B+eF8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Operation No" = _t, Material = _t, #"Scrap Rate" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Operation No", Int64.Type}, {"Material", Int64.Type}, {"Scrap Rate", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Accumulate(Table.SelectRows(#"Changed Type",(z)=>z[Operation No]<=[Operation No])[Material] ,0,(x,y)=>x+y) ), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.ToRows(Table.SelectColumns(Table.SelectRows(#"Added Custom",(z)=>z[Operation No]<[Operation No] ),{"Custom", "Scrap Rate"}) )), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", (x)=> List.Accumulate({1..Table.RowCount(Table.SelectRows(#"Added Custom1",(z)=>z[Material]<=x[Material])) } , x[Custom.1] , (x,y)=> List.InsertRange(x,y*2-2,{{1,0}}) ) ), Custom2 = Table.AddColumn(#"Added Custom2", "Custom.3", each List.Accumulate([Custom.2],{{},0},(x,y)=>{ x{0}&{x{1}} , let a=if List.Sum(x{0}) =null then 0 else List.Sum(x{0}) in (y{0} +a ) *y{1} } ) ), #"Added Custom3" = Table.AddColumn(Custom2, "Custom.4", each (List.Sum([Custom.3]{0}) +[Custom])*[Scrap Rate] ), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom", "Custom.1", "Custom.2", "Custom.3"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.4", "Value"}}) in #"Renamed Columns"Then we can get the result as follows:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
I've managed to sort this out by using separate columns as iterations. This gets over the circular reference error. See example code below.
- v-yueyunzh-msft
Community Support
Hi, Rob_Jackson
For your needs, I have tested and tried for a long time in my local. But I think Power BI may not be able to meet your needs. Because your needs involve loop iterations, and you also need to sum the values of previous iterations.
Power BI's DAX first does not have a function similar to taking the value of the previous row, unless it is a simple accumulation.
The Loop function does exist in Power Query, but it can only get the value of the previous row, but cannot get the cumulative value of all previous calculations.
Therefore, for your needs, I suggest that you can implement them in your external ETL tool, and then import them into Power BI Desktop for data display.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- v-yueyunzh-msft
Hi , Rob_Jackson
As i test for a very long long time in my side , i realize it in Power Query !~
If this can help you , you can try it in the "Advanced Editor":
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcrBDcAwCAPAXXhHFYFAyCwo+69Rl195INlnMmnSqONHQtgm3ZEkAKnha4qk9cImrlK4AKujAayjA7zjBuyOAYiOB3B+eF8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Operation No" = _t, Material = _t, #"Scrap Rate" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Operation No", Int64.Type}, {"Material", Int64.Type}, {"Scrap Rate", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Accumulate(Table.SelectRows(#"Changed Type",(z)=>z[Operation No]<=[Operation No])[Material] ,0,(x,y)=>x+y) ), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.ToRows(Table.SelectColumns(Table.SelectRows(#"Added Custom",(z)=>z[Operation No]<[Operation No] ),{"Custom", "Scrap Rate"}) )), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", (x)=> List.Accumulate({1..Table.RowCount(Table.SelectRows(#"Added Custom1",(z)=>z[Material]<=x[Material])) } , x[Custom.1] , (x,y)=> List.InsertRange(x,y*2-2,{{1,0}}) ) ), Custom2 = Table.AddColumn(#"Added Custom2", "Custom.3", each List.Accumulate([Custom.2],{{},0},(x,y)=>{ x{0}&{x{1}} , let a=if List.Sum(x{0}) =null then 0 else List.Sum(x{0}) in (y{0} +a ) *y{1} } ) ), #"Added Custom3" = Table.AddColumn(Custom2, "Custom.4", each (List.Sum([Custom.3]{0}) +[Custom])*[Scrap Rate] ), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom", "Custom.1", "Custom.2", "Custom.3"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.4", "Value"}}) in #"Renamed Columns"Then we can get the result as follows:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
