Forum Discussion
Tutorial Request Evaluate and Estimate
Good afternoon!
Does Power BI have the ability to evaluate and quantify based off current inventory?
Standard: 4 tires and 2 windshield wiper blades = 1 car
Inventory: 11 tires and 4 windshield wiper blades = 2 cars with three remaining tires
Thank you very much!
Mac
Hi, AsherPSmear , you might want to have a look at this solution in Power Query; here's the attached file for details.
let Assembly = (inventory as table) => let item_inv = inventory[ITEM], item_std = Standard[ITEM], missing = List.RemoveItems(item_std, item_inv), item_other = List.RemoveItems(item_inv, item_std), other = Table.RemoveColumns(Table.SelectRows(inventory, each List.Contains(item_other, [ITEM])), {"STORE"}), assembly = if List.Count(missing) > 0 then Table.FromRecords({[ITEM="Car", QUANTITY=0]} & Table.ToRecords(inventory)) else [ a = Table.Sort(Table.SelectRows(inventory, each List.Contains(item_std, [ITEM])),{{"ITEM", Order.Ascending}}), indexed = Table.AddIndexColumn(a, "Index"), b = Table.AddColumn(indexed, "car", each Number.IntegerDivide([QUANTITY], Standard[QUANTITY]{[Index]})), num = List.Min(b[car]), c = Table.AddColumn(indexed, "leftover", each [QUANTITY]-Standard[QUANTITY]{[Index]}), d = Table.FromRecords({[ITEM="Car", QUANTITY=num]} & Table.ToRecords(c)) & other ][d] in assembly, Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrxDHINBtKGRkqxOhChcE8/l2APT1cfF4VwzwDXIAUnH0cXV6CECUJNsKtjCJAyNgCLOCEZZAoXwWmOOVwJ1BhDhDH+IR5AlQGOQSEgw8zA4s5IxhtZwIVgmoGuigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [STORE = _t, ITEM = _t, QUANTITY = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"STORE", type text}, {"ITEM", type text}, {"QUANTITY", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"STORE"}, {{"All", each _, type table [STORE=nullable text, ITEM=nullable text, QUANTITY=nullable number]}}), #"Added Custom" = Table.TransformColumns(#"Grouped Rows", {{"All", each Assembly(_)}}), #"Expanded All" = Table.ExpandTableColumn(#"Added Custom", "All", {"ITEM", "QUANTITY"}, {"ITEM", "QUANTITY"}) in #"Expanded All"Enjoy M, enjoy Power Query!
4 Replies
AsherPSmear
DAX is a powerful functional language that can perform complicated calculations leveraging the evaluation context on a well-built data model. Please provide sample data with your example along with the expected output.
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?
_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.Click on the Thumbs-Up icon if you like this reply 🙂
Thank you for the reply. I have uploaded the excel and .pbix on Google Drive. I am trying to estimate how many vehicles can be serviced based on template and current inventory.
https://drive.google.com/drive/folders/1ZdJYJDXczWnFuq1q2SHdBN4yxVT5nyHr?usp=sharingThank you!
Mac
Hi, AsherPSmear , you might want to have a look at this solution in Power Query; here's the attached file for details.
let Assembly = (inventory as table) => let item_inv = inventory[ITEM], item_std = Standard[ITEM], missing = List.RemoveItems(item_std, item_inv), item_other = List.RemoveItems(item_inv, item_std), other = Table.RemoveColumns(Table.SelectRows(inventory, each List.Contains(item_other, [ITEM])), {"STORE"}), assembly = if List.Count(missing) > 0 then Table.FromRecords({[ITEM="Car", QUANTITY=0]} & Table.ToRecords(inventory)) else [ a = Table.Sort(Table.SelectRows(inventory, each List.Contains(item_std, [ITEM])),{{"ITEM", Order.Ascending}}), indexed = Table.AddIndexColumn(a, "Index"), b = Table.AddColumn(indexed, "car", each Number.IntegerDivide([QUANTITY], Standard[QUANTITY]{[Index]})), num = List.Min(b[car]), c = Table.AddColumn(indexed, "leftover", each [QUANTITY]-Standard[QUANTITY]{[Index]}), d = Table.FromRecords({[ITEM="Car", QUANTITY=num]} & Table.ToRecords(c)) & other ][d] in assembly, Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrxDHINBtKGRkqxOhChcE8/l2APT1cfF4VwzwDXIAUnH0cXV6CECUJNsKtjCJAyNgCLOCEZZAoXwWmOOVwJ1BhDhDH+IR5AlQGOQSEgw8zA4s5IxhtZwIVgmoGuigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [STORE = _t, ITEM = _t, QUANTITY = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"STORE", type text}, {"ITEM", type text}, {"QUANTITY", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"STORE"}, {{"All", each _, type table [STORE=nullable text, ITEM=nullable text, QUANTITY=nullable number]}}), #"Added Custom" = Table.TransformColumns(#"Grouped Rows", {{"All", each Assembly(_)}}), #"Expanded All" = Table.ExpandTableColumn(#"Added Custom", "All", {"ITEM", "QUANTITY"}, {"ITEM", "QUANTITY"}) in #"Expanded All"Enjoy M, enjoy Power Query!
