Calculate QTY for a two part IF function

[Jorjor]

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])

[Anonymous · Accepted solution]

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]

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

[rajendraongole1]

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]

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.