Tutorial Request Evaluate and Estimate

[AsherPSmear]

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

[Fowmy]

@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 🙂

YouTube, LinkedIn

[AsherPSmear]

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

[CNENFRNL · Accepted 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!

[AsherPSmear]

@CNENFRNL

Thank you very much!

Mac