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.

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.