Division with filtered field

[ThiagoMi88]

Hello guys,

I have a table where one column has the products manufactured, in another column the raw materials, 
but there is also the record of the manufactured product, as you can see below. In the third column 
I have the amount of raw material used and the manufactured quantity of the product.
I need the quantity of raw material per unit of the product manufactured, so I need to divide the 
quantity of each raw material by the quantity of the product, which is in the same column.

Product	Component	Amount
Pro11	RawMaterial1	10
Pro11	RawMaterial2	12
Pro11	RawMaterial3	6
Pro11	RawMaterial4	4
Pro11	RawMaterial5	2
Pro11	Pro11	20

In this example, I would have to divide the quantity of the rawmaterial1 by the quantity of the product (10/20 = 0.5).
That is, we used 0.5 of the rawmaterial1 for each 1 of Pro11

[v-juanli-msft · Accepted solution]

Hi @ThiagoMi88 

Create two measures

amount_product =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Production Order], Table1[Product] ),
        LEFT ( Table1[Raw Material], 3 ) = "Pro"
    )
)

Expected result = MAX(Table1[Amount])/[amount_product]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

This calculated column formula works

=[Amount]/CALCULATE(SUM(Data[Amount]),FILTER(Data,Data[Component]=EARLIER(Data[Product])))

Hope this helps.

[ThiagoMi88]

First of all,

Thanks for the answers.

I forgot a very important field of the table, "production order".

It can occur in 2 different production orders, used to produce the same product, different amounts of raw material are used.

In this way the amount of raw material used in each production order would be different.

[Ashish_Mathur]

Hi,

In another column, Ssow the exact result you are expecting.  Also, share your data in a format that can be pasted in an Excel file.

[ThiagoMi88]

It's ok,

Here is the worksheet

https://docs.google.com/spreadsheets/d/e/2PACX-1vSau7otZ-Ep1KSptceJUHXrFXP8WzQ-Orqx6Xg-m-ytEypIupjb7...

[Ashish_Mathur]

Hi,

I cannot download your workbook from that link.  Share the link from where i can download your workbook and please ensure that the expected result column is formula driven so that i can understand the logic.

[ThiagoMi88]

Follow the link

https://docs.google.com/spreadsheets/d/e/2PACX-1vSau7otZ-Ep1KSptceJUHXrFXP8WzQ-Orqx6Xg-m-ytEypIupjb7...

Sorry. The logic is the same as I described earlier, I just forgot to mention the Production Order field.

[Ashish_Mathur]

That link leads to an error page.

[AlB]

Hi @ThiagoMi88 

Create a new calculated column in the table you show:

NewCalcColumn =
IF (
    Table1[Product] <> Table1[Component],
    DIVIDE (
        Table1[Component],
        LOOKUPVALUE (
            Table1[Amount],
            Table1[Product], Table1[Product],
            Table1[Component], Table1[Product]
        )
    )
)

[ThiagoMi88]

Hi @AlB 

Thanks for the sugestion.

I tried to apply the formula that passed me, but it is displaying the following error:

A multi-valued table was provided, and a single value was expected.

[AlB]

works fine on my end with the example provided. If you get that error you probably have more than one row with Product and Component = 'Pro11'

[ThiagoMi88]

You're right, there's another column called "production order". There may be more than one combination 
Product and Component = 'Pro11'.  

I tried:

NewCalcColumn =
IF (
    Table1[Product] <> Table1[Component],
    DIVIDE (
        Table1[Amount],
        LOOKUPVALUE (
            Table1[Amount],
            Table1[PO], Table1[PO],
            Table1[Product], Table1[Product],
            Table1[Component], Table1[Product]
        )
    )
)

But didn't work.