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

jprlimey · January 18, 2021, 7:39pm

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

Rejects 1

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)

Melissa · January 20, 2021, 8:37pm

Hi @jprlimey,

Just a quick update, I haven’t forgotten about this.
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.

jprlimey · January 23, 2021, 12:32pm

@Melissa

Really appreciate your persistence in attempting to solve this problem.

Best Regards
J

Melissa · January 27, 2021, 10:17am

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

EnterpriseDNA · January 29, 2021, 12:01am

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!

jprlimey · January 29, 2021, 1:27pm

@Melissa,

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

Hope this is helpful.

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

Melissa · January 31, 2021, 11:43am

Thanks @jprlimey that was helpful.

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(
        ADDCOLUMNS(
            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)
I hope this is helpful.

EnterpriseDNA · February 2, 2021, 1:08am

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!

jprlimey · February 2, 2021, 3:01am

@Melissa,

I should have time tomorrow to review your solution.

I’ll report back shortly

Regards
J

jprlimey · February 2, 2021, 7:27pm

@Melissa,

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

Melissa · February 2, 2021, 9:05pm

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…