I have a rather unique scenario where I need to calculate the standard costs on a work order from the step number on the work order where a technician has produced a scrap or reject part.
At the step number where the scrap/reject was recorded I need to determine the standard costs (I have the standard costs already) multiplied by the scrap/reject quantity, then going backwards to each preceding step on the work order determine the standard costs multiplied by the scrap/reject quantity.
For Example we have a work order identified as 12345 with step number 1 thru 10 and at step 6 we scrap/reject something, now get the standard costs for steps 6, 5, 4, 3, 2 and finally 1, multiplied by the scrapped/rejected quantity at step 6. This provides the true scrap/reject costs when all 6 steps are totaled.
The image below shows how we determine the standard costs, for simplicity we show the standard cost for a unit of 1. Under the Reject Qty column we show how many were rejected, all the information shown in this table comes from 1 of 2 fact tables, this essentially captures the production information or shop floor activity if you will. This image shown pulls information from the Confirmed table.
The second image below details the Scrap/Reject activity, this is the table where we need to show the total accumulated standard costs for the scrap/reject quantity of product. the unique identifiers here would be Order and Op No columns. This image pulls information from the Reject table.
The third image below shows what we need for an end result, the last column shows the accumulated standard costs for the rejected quantity.
I wrote the following DAX to determine the accumulated standard costs but it does not work, what am I doing wrong?
Reject Costs 1 =
VAR _Order = SELECTEDVALUE( 'Rejects'[Order] )
VAR _OpNo = SELECTEDVALUE( 'Rejects'[Op No] )
VAR _RejectQty = SELECTEDVALUE( 'Rejects'[Qty] )
VAR _Scrapcost = _RejectQty * [Std Costs Unit 1]
VAR _CumulativeCosts =
CALCULATE( _ScrapCost ,
FILTER( ALLSELECTED( 'Confirmed' ), 'Confirmed'[Op No] >= MIN ( 'Rejects'[Op No] ) &&
'Confirmed'[Order] = _Order
)
)
RETURN
_Scrapcost
I also have a further problem where I’m not seeing totals in some columns. for example the first image above shows no total for Std Costs Unit 1. I applied the iteration SUMX to the virtual table _SumStdCost, then verified if column Matl No HASONEVALUE, if it does then pass along the _Result variable, if not SUMX the [@Sum Std Cost] from the virtual table _SumStdCost. I thought this should have returned a value, but it didn’t. What am i doing wrong?
Std Costs Unit 1 =
VAR _ActivityType = SELECTEDVALUE( 'Confirmed'[Activity Type] )
VAR _Rate =
LOOKUPVALUE ( ActyAcct[Std Rate] ,
'ActyAcct'[Acty Acct] , _ActivityType )
VAR _labormachhrs = [Lab Mach Std]
VAR _Result = _labormachhrs * _Rate
VAR _SumStdCost =
SUMMARIZE(
Confirmed,
'Confirmed'[Matl No] ,
'Confirmed'[Order],
'Confirmed'[Op No] ,
'Confirmed'[Activity Type] ,
"@Sum Std Cost" , _Result
)
RETURN
IF( HASONEVALUE( 'Confirmed'[Matl No] ) ,
_Result ,
SUMX( _SumStdCost , [@Sum Std Cost] )
)
Below is the pbix file. Hopefully someone can help me out and get me back on track.
Regards
J
Example 1 Rev-2.pbix (376.5 KB)
