Forum Discussion

ThiagoMi88's avatar
ThiagoMi88
Regular Visitor
7 years ago
Solved

Division with filtered field

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.

ProductComponentAmount
Pro11RawMaterial110
Pro11RawMaterial212
Pro11RawMaterial36
Pro11RawMaterial44
Pro11RawMaterial52
Pro11Pro1120
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
 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

12 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      ThiagoMi88
      Regular Visitor
      7 years ago

      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's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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'

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    This calculated column formula works

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

    Hope this helps.

    • ThiagoMi88's avatar
      ThiagoMi88
      Regular Visitor
      7 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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.