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.

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'

      • ThiagoMi88's avatar
        ThiagoMi88
        Regular Visitor
        7 years ago

         

        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.