topic Re: Calculate QTY for a two part IF function in DAX Commands and Tips

Calculate QTY for a two part IF function

Jorjor — Mon, 01 Jul 2024 17:06:30 GMT

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

Re: Calculate QTY for a two part IF function

rajendraongole1 — Mon, 01 Jul 2024 17:12:50 GMT

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!!

Re: Calculate QTY for a two part IF function

Jorjor — Mon, 01 Jul 2024 17:22:03 GMT

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.

Re: Calculate QTY for a two part IF function

Anonymous — Tue, 02 Jul 2024 05:48:40 GMT

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.

Re: Calculate QTY for a two part IF function

Jorjor — Tue, 02 Jul 2024 12:22:16 GMT

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