# Accumulated Costs in Reverse Order Incorrect On Multiple Occurrences of Rejects of

This is a continuation of a previously closed topic. Refer to https://forum.enterprisedna.co/t/calculate-associated-total-costs-of-context-in-reverse-order/12987/5

After doing more analysis, it turns out that we are correctly calculating the rejects costs when we have a single occurrence of a reject on a production order. In the image below we are showing the Reject table

As you can see the first record for Order A11682 Op No 50, we lost 15 parts for LEAK, the Cumulative Correct Reject Costs 2 measure returns \$393.81, this is correct.

However looking closer where multiple rejects occurred on the same production order we seem to have incorrect values for Cumulative Correct Reject Costs 2 measure. The image below is from the Confirmed table.

To this table I added an additional measure named Cumulative Std Costs Unit 1.

``````Cumulative Std Costs Unit 1 =
VAR _CurrentOrder =
SELECTEDVALUE ( Confirmed[Order] )
VAR _CurrentOpNO =
SELECTEDVALUE ( Confirmed[Op No] )
VAR _MaxOPNo =
MAX ('Confirmed'[Op No] )
VAR _CumulativeStdCosts =
SUMX (
CALCULATETABLE (
SUMMARIZE (
Confirmed,
Confirmed[Activity Type],
Confirmed[Matl No],
Confirmed[Op No],
Confirmed[Order],
Confirmed[Std Rate]
),
Confirmed[Op No] <= _MaxOPNo,
Confirmed[Order] = _CurrentOrder,
REMOVEFILTERS ( Confirmed )
),
[Std Costs Unit 1]
)
VAR Result = _CumulativeStdCosts
RETURN
Result
``````

This calculates the accumulated standard costs from the first Op No to the last Op No for each production order. Very quickly you can see that for Order A11909 Op No 130 we have \$43.30, whereas in the Reject table for the same order number we have 4 separate rejects, 3 of which are tied to Op No 130. As you can see the Cumulative Correct Reject Costs 2

``````Cumulative Correct Reject Costs 2 =
VAR _CurrentOrder =
SELECTEDVALUE ( 'Rejects'[Order] )
VAR _CurrentOpNO =
SELECTEDVALUE ( Rejects[Op No] )
VAR _MaxOPNo =
SELECTEDVALUE( Rejects[Op No] )
VAR _RejectQty =
SELECTEDVALUE( 'Rejects'[Qty] )
VAR _CumulativeStdCosts =
SUMX (
CALCULATETABLE (
SUMMARIZE (
Confirmed,
Confirmed[Activity Type],
Confirmed[Matl No],
Confirmed[Op No],
Confirmed[Order],
Confirmed[Std Rate]
),
FORMAT('Confirmed'[Op No], "@") <= _MaxOPNo ,
//Confirmed[Op No] <= _MaxOPNo,
Confirmed[Order] = _CurrentOrder,
REMOVEFILTERS ( Confirmed )
),
[Std Costs Unit 1]
)
VAR Result = _RejectQty * _CumulativeStdCosts
RETURN
Result
``````

has returnedâ€¦

\$ 122.54 for qty 8 rejected for Depth â€¦ should be \$43.30 * 8 = \$364.40
\$275.72 for qty 18 rejected for O/S â€¦ should be \$43.30 * 18 = \$779.40
\$61.27 for qty 4 rejected for Depth â€¦ should be \$43.30 * 4 = \$173.2

and finally for Op no 140â€¦

\$15.84 for qty 1 rejected for Depth â€¦ should be \$43.82 * 1 = \$43.82

I pondered this for some time yesterday, trying different variants of the
Cumulative Correct Reject Costs 2 measure but was unsuccessful in resolving. I also checked my production model and I see the same issue.

I have attached the latest .pbix file

Not sure of where to go from here, as Iâ€™d previously stated we had a solution for the posting.

Best Regards
J

Example 1 Rev-4.pbix (385.9 KB)

Hi @jprlimey,

After reviewing it again Iâ€™m sorry to report I havenâ€™t been unable to crack it yet but Iâ€™ll give it another try this weekend if it remains unsolved.

@Melissa

Really appreciate your persistence in attempting to solve this problem.

Best Regards
J

Hi @jprlimey,

Havenâ€™t worked it out yet but it would be a big help if you can reproduce the values for order A11909 in Excel just using Excel formulas and share that - thanks.

Order A11909 Op No 130 we have \$43.30
Order A11909 Op No 140 we have \$43.82

1 Like

Hi @jprlimey, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Attached is an Excel file you requested.

Basically I exported the 2 tables from the pbix model and added them into the workbook attached.

to get the correct values in the eDNA Rejects Table Rev-1 tab (which represents the Reject table), I used an INDEX MATCH function against the eDNA Confirmed Table Rev-1 (which represents the Confirmed table).

Best Regards
J
eDNA Melissa Rev-1.xlsx (27.3 KB)

However for now Iâ€™m going to admit defeatâ€¦ I do have a solution but that requires 2 calculated columns and I havenâ€™t found a way around that (yet). Here it goes.

In the Confirmed table added this calculated column.

``````Std Costs per Unit =
VAR myOrder = Confirmed[Order]
VAR myOpNo = VALUE(Confirmed[Op No])
VAR myTable =
CALCULATETABLE(
SUMMARIZE( Confirmed, Confirmed[Activity Type], Confirmed[Matl No], Confirmed[Op No], Confirmed[Order], Confirmed[Std Rate] ),
"@StdCost", [Lab Mach Std] * [Std Rate]
),
VALUE(Confirmed[Op No]) = myOpNo,
Confirmed[Order] = myOrder
)
RETURN

SUMX( myTable, [@StdCost] )
``````

and in the Rejects table added this calculated column.

``````Cum Std Costs per Unit =
VAR myOrder = Rejects[Order]
VAR myOpNo = VALUE(Rejects[Op No])
VAR myTable =
CALCULATETABLE(
SUMMARIZE( Confirmed, Confirmed[Activity Type], Confirmed[Matl No], Confirmed[Op No], Confirmed[Order], Confirmed[Std Costs per Unit] ),
VALUE(Confirmed[Op No]) <= myOpNo,
Confirmed[Order] = myOrder,
REMOVEFILTERS( Confirmed )
)
RETURN

SUMX( myTable, [Std Costs per Unit] )
``````

And finally the measure

``````Cum Reject Cost =
VAR _CurrentOrder = COALESCE( SELECTEDVALUE ( Confirmed[Order] ), SELECTEDVALUE ( Rejects[Order] ))
VAR _CurrentOpNO = COALESCE( SELECTEDVALUE ( Confirmed[Op No] ), SELECTEDVALUE( Rejects[Op No] )) *1
VAR _MaxOPNo = COALESCE( MAX ('Confirmed'[Op No] ), MAX( Rejects[Op No] )) *1
VAR _CumulativeStdCosts =
SUMX(
CALCULATETABLE(
SUMMARIZE(
Confirmed,
Confirmed[Activity Type],
Confirmed[Matl No],
Confirmed[Op No],
Confirmed[Order],
Confirmed[Std Rate]
),
VALUE( Confirmed[Op No] ) <= _MaxOPNo,
Confirmed[Order] = _CurrentOrder,
REMOVEFILTERS( Confirmed )
),
[Std Costs Unit 1]
)
VAR Result =
IF( ISINSCOPE( Confirmed[Order] ),
_CumulativeStdCosts * [Reject Qty],
SUMX( Rejects, [Cum Std Costs per Unit] * Rejects[Qty] )
)

RETURN
Result
``````

With this result

Hereâ€™s your sample file. Example 1 Rev-4 (2).pbix (387.4 KB)

2 Likes

Hi @jprlimey , did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

I should have time tomorrow to review your solution.

Iâ€™ll report back shortly

Regards
J

Finally had the chance to spend some time on this, things are crazy busy.

This is some solution, great job,. So why was this one so difficult, it would be good to understand why, so that I could avoid similar traps next time?

Yes, we have finally cracked it, now my task is to apply this to my real data, this has been a gigantic effort by all the experts that have contributed to this solution. Without doubt this is by far the best Power BI forum!

I really like the introduction of COALESCE, but what do the * 1 do per below

``````VAR _CurrentOpNO = COALESCE( SELECTEDVALUE ( Confirmed[Op No] ), SELECTEDVALUE( Rejects[Op No] )) *1
VAR _MaxOPNo = COALESCE( MAX ('Confirmed'[Op No] ), MAX( Rejects[Op No] )) *1
``````

I must admit I had already started to ponder other options to get the required result, which would have been real clunky to say the least.

Thanks again to all for assisting on this solution. now to put into my real model.

Best Regards
J

Hi @jprlimey,

Glad to hear this works for you.

As for the *1 DAX allows for type conversion - your field Op No was formatted as text and I needed it to be a number so multiplying by 1 converts the text into a number.

In regard to the difficulty, to calculate the result you need to iterate both fact tables, first Confirmed for the Standard Cost next the Rejects for the Cum std Cost, â€śall virtuallyâ€ť this had me chasing my tailâ€¦