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 t...
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.

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.