Forum Discussion

Jorjor's avatar
Jorjor
Frequent Visitor
2 years ago
Solved

Calculate QTY for a two part IF function

I want to calculate the QTY for each component in my data set. The forumula I'm using is only calculating for the top item not all of them.

 

BOM Q = SUMX(FILTER('All Demand',[BOM ID]=EARLIER('WORKORDERS_OPEN'[BOM ID]) && [Component]=EARLIER('WORKORDERS_OPEN'[Component])),[BOM QTY]*[REPORTREMAINASFINISHED])
 
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Jorjor ,

     

    You can try formula like below to calculate QTY = Remanin finish * BOM QTY:

    QTY 1 =
VAR CurrentBOMID = 'RemainingFinishTable'[BOM ID]
VAR CurrentComponent = 'RemainingFinishTable'[Component]
VAR BOMQTY =
    CALCULATE (
        SUM ( 'BOMTable'[BOM QTY] ),
        FILTER (
            'BOMTable',
            'BOMTable'[BOM ID] = CurrentBOMID
                && 'BOMTable'[Component] = CurrentComponent
        )
    )
RETURN
    BOMQTY * 'RemainingFinishTable'[Remain Finish]

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Jorjor -create a measure to calculate the QTY for each component

     

    QTY Measure =
    SUMX(
    FILTER(
    'All Demand',
    'All Demand'[BOM ID] = 'WORKORDERS_OPEN'[BOM ID]
    && 'All Demand'[Component] = 'WORKORDERS_OPEN'[Component]
    ),
    'All Demand'[BOM QTY] * 'WORKORDERS_OPEN'[REPORTREMAINASFINISHED]
    )

     

    Hope it works for both components.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • Jorjor's avatar
      Jorjor
      Frequent Visitor
      2 years ago

      Hi this is having the same issue it is only returning the top value of the BOM not all of the values for each component.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Jorjor ,

     

    You can try formula like below to calculate QTY = Remanin finish * BOM QTY:

    QTY 1 =
VAR CurrentBOMID = 'RemainingFinishTable'[BOM ID]
VAR CurrentComponent = 'RemainingFinishTable'[Component]
VAR BOMQTY =
    CALCULATE (
        SUM ( 'BOMTable'[BOM QTY] ),
        FILTER (
            'BOMTable',
            'BOMTable'[BOM ID] = CurrentBOMID
                && 'BOMTable'[Component] = CurrentComponent
        )
    )
RETURN
    BOMQTY * 'RemainingFinishTable'[Remain Finish]

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Jorjor's avatar
      Jorjor
      Frequent Visitor
      2 years ago

      Still getting the same error think it is an issue with my data as a whole thanks for the help.