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.
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
Solved! Go to Solution.
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!
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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=sharing
Thank 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!
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |