Calculate Associated Total Costs of Context in Reverse Order

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.

prod ord var-1

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.

prod ord var-2

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)

I’ve noticed that lots of users has looked at this problem but no one has offered any guidance/solution, so perhaps I need to ask the question(s).

  1. Is what I’m asking not achievable?
  2. Have I not presented the problem with sufficient detail to warrant a response?
  3. After doing more research via EDNA video, I came across one that Sam has done on Crossjoin and couldn’t help but think applying this technique into a new table may be a path forward?

Regards
J

So obviously this must be more difficult to resolve then I envisioned, so to help progress towards a solution, I’ll try to simplify things a little as I think the relationships look a little messy. When I can sneak away, out of sight of my wife, as I’m home for the holidays, I’ll clean up the relationships removing all the lookup tables and re-post the pbix file.
In the relationship as it currently stands we have 2 main fact tables Confirmed and Rejects, in between these is a bridge table with unique Order Number and Op No, each of which are present in the fact tables. My thought process was that this would provide the linkage for my DAX ( shown in original post above) would work, but perhaps my approach is completely of the mark.

So if anyone feels like a real challenge, I’m open to suggestions and hopefully a possible solution. I hope to re-post an update pix file tonight.

Regards
J

@jprlimey I have been working on your request and it’s not that difficult a task but somehow I am getting strange results. If you could simplify your pbix file then we can provide a solution and then will try to figure out in the master pbix file as to why getting the strange results.

Thanks.

Thank MudassirAli,

I have simplified the model slightly, by remove relationships on some of the lookup tables, leaving only what is required for the DAX shown in original post, hope this helps.

The image below shows the current relationships.
From the image i stand corrected, i have 3 fact tables, i previously stated only 2. Between each fact table i have bridge table a Matl No table between Reject and Confirmed tables, and Order table between Operations and Confirmed. The bridge tables I’m thinking are essential to make this work.

I will also include the Excel files in the event they are also needed.

Example 1 Rev-3.pbix (375.4 KB)
ActyAct Rev-1.xlsx (10.0 KB) Confirmations Rev-4.xlsx (30.0 KB) Operations Rev-4.xlsx (34.4 KB) Rejects Rev-3.xlsx (14.5 KB)

Many thanks
J

@jprlimey Thanks. I will head out soon and will work on it at home.

@jprlimey The cumulative total and incorrect total part has been fixed with the following measures with the help of our DAX Expert @AntrikshSharma.

Cumulative Correct Reject Costs =
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 = [Reject Qty] * _CumulativeStdCosts
RETURN
    Result  


Fix Total =
SUMX (
    SUMMARIZE (
        Confirmed,
        Confirmed[Activity Type],
        Confirmed[Matl No],
        Confirmed[Op No],
        Confirmed[Order],
        Confirmed[Std Rate],
        "@Val", [Std Costs Unit 1]
    ),
    [@Val]
)

However, can you elaborate more on the third requirement?

Attaching the files for your reference.

ActyAct Rev-1.xlsx (10.0 KB) Confirmations Rev-4.xlsx (30.0 KB) Example 1 Rev-3.pbix (376.7 KB) Operations Rev-4.xlsx (34.4 KB) Rejects Rev-3.xlsx (14.5 KB)

Thanks.

5 Likes

Thank you MudassirAli and AntrikshSharma for working through this problem.

After looking over the DAX you provided Cumulative Correct Costs, my intent was that the Rejects table is where the measure should be placed, from the measure i see you are getting the SelectedValue from the Confirmed table for VAR _CurrentOrder _CurrentOpNo and _MaxOpNo. Whereas the Current Order number, OpNo should come from the Reject table, so I’m not sure this will actually work.

In the second image in original post for example we have the first row shows order number A11199 and OpNo 60 shows qty 26 rejected. Now taking this information we need to compute the total reject costs from the Confirmed table from Op 60 backwards to Op 10, by taking the Standard Costs Unit 1 measure and multiplying it the the Reject Qty of 26 from the Reject table. I could be completely wrong with my summary I have just written, I need to get to my laptop to see if this solution actually works when the Cumulative Correct Costs measure is placed in Reject table. I’ll report back shortly.

Thanks
J

MudassirAli,

My initial thoughts are correct. See image below, this is the Confirmed table, i need the measure to go into the Rejects table, how would we accomplish this. We are very close to a final solution!

You also mention the 3rd problem, after re-reading my posts i’m not sure what is the 3rd question could you provide more detail please.

Best Regards
J

MudassirAli,

Any further thoughts on a possible solution?

Regards
J

@MudassirAli @AntrikshSharma,

We are extremely close to a final solution for my issue, as I described in my initial post the cumulative reject costs must be shown in the Rejects table, hence the solution provided which are placed in the Confirmed table actually do show the desired results.

The Rejects table is the most accurate information we work with, as some of the reject information is on occasion dropped by our ERP system which populated the Confirmed table, whereas our Reject table comes from a different data source and is absolutely accurate.

So how can we get the accumulated reject costs to show in the Rejects table by using the SELECTEDVALUES for Order Number, Op No and Quantity rejected from the Reject table. Such that we get the corresponding Std Unit Cost 1 for the SELECTEDVALUES from Reject table for the Order Number and Op No for the matching Order Number and Op No in the Confirmed table, where we must calculate the accumulated costs…

For example Order Number 3906 Op No 60 shows 26 rejected parts in the Reject table (see image 3 titled prod ord var-3 in the initial post.
Using the SELECTEDVALUES for both Order Number, Op No and Qty rejected, we then extract from the Confirmed table the Std Costs Unit 1 from Op No 60 , 50 , 40 , 30, 20 and 10 and multiply the accumulated Std Costs Unit 1 by the Qty Rejected from the Reject table, in this case the result would be $4954.62.

I have checked the values returned by the DAX measure below, which does appear to be returning the correct values (although I might add we do seem to have some rounding issues, but we will deal with that later), so I know we are on the right track.

Cumulative Correct Reject Costs =
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 = [Reject Qty] * _CumulativeStdCosts
RETURN
    Result

Any help would be greatly appreciated.

Best Regards
J

@jprlimey sorry I have been busy. I will look into this one.

Any chance at looking at this problem further

Regards
J

@MudassirAli @AntrikshSharma

I apologize in advance for being so persistent in driving towards a final solution. I’m lagging behind on completing this report.

Is what I’m asking for exceedingly difficult, or is it fairly straightforward?
Do we need to bring other experts into the discussion?
If we can’t move forward what are some possible suggestions of either re-configuring the problem or doing something entirely different?

Again, your collective efforts I appreciate and I do realize we have all just come-off our Xmas and New Years breaks.

Best Regards
J

I keep trying to fix this issue but to no avail. 

I re-wrote the DAX to get the result into the Reject table, where i really need it, see below.

I changed the SELECTEDVALUE to reference the Reject table, however it still doesn’t work.

I end up with the following error

Error

I need the result of the DAX in the Rejects table, see yellow highlite

Rejects

The Confirmed table is shown below. The Cumulative Correct Reject Cost measure is producing the correct result, we just need it in the Reject table

Modified DAX below…

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]
            ),
            Confirmed[Op No] <= _MaxOPNo,
            Confirmed[Order] = _CurrentOrder,
            REMOVEFILTERS ( Confirmed )
        ),
        [Std Costs Unit 1]
    )
VAR Result = _RejectQty * _CumulativeStdCosts
RETURN
    Result  

I also included the latest pbix file.

Any help would be greatly appreciated

Thanks
J

Example 1 Rev-4.pbix (382.6 KB)

It would appear lots of people are looking at this post but unfortunately nobody other than @MudassirAli and @AntrikshSharma is offering any assistance. Where do I go from here?

Thanks J

Hi @jprlimey,

So if you’d read the error message you know there is an inconsistency in the format of your data between the two tables. On inspection Confirmed[Op No] is formatted as a number while the Rejects[Op No] is formatted as text. I chose to use the FORMAT function to sort this but ideally you would resolve this in the data model by making sure these identical fields have an identical format.

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[Order] = _CurrentOrder,
            REMOVEFILTERS ( Confirmed )
        ),
        [Std Costs Unit 1]
    )
VAR Result = _RejectQty * _CumulativeStdCosts
RETURN
    Result

This measure can now be visualized in the table BUT not all figures match like illustrated below. I haven’t examined why that is - I’ll leave that to you.

1 Like

@Melissa,
Thank you kindly for responding. And yes, it looks like I’m wearing the “goat horns”, I didn’t check the format types for the SELECTEDVALUES, my fault entirely.

I did put in your revised DAX and it working nicely. However the data I provided for this post, is masked greatly from what my actual model looks like, information was too sensitive to share.

Once I put the DAX into my actual model it would only return the Std Cost Unit 1 for the matching Order and Op No in the second table “Confirmed”, it’s almost like it’s not SUMX ing the Std Cost Unit 1. I’ll get time in the morning to post some images to assist.

Regards
J

@Melissa

After working this issue some more today I believe we have a working solution. I reported yesterday that when adding the DAX to my actual production model, the result was only returning a single value from from the Std Cost Unit 1column, where we needed the DAX to calculate for the matching Order and Op No between the Rejects and Confirmations tables, backwards to the first operation.

Image 1 below shows the confirmations table (sensitive data is masked)

You can clear see that the Cumulative Correct Reject Cost column is summing correctly. Ex Op 20 shows $51 ($49.20 + $1.80 from Std Unit Cost 1 column), you can also see that Op No 80 shows $429.27 (summing from Op 80 to Op 10 in the Std Unit Cost 1 column does equal $429.27).

So when i added the DAX to my Rejects table, i didn’t get the expected results. The Cumulative Correct Reject Costs returned the single value from the matching Op No in the Confirmations table Std Unit cost 1 column, see Image 2 below

Image 2

I then removed the Work Center column from the Rejects table and i did finally get the correct results, see Image 3 below.
Image 2

So it really looks like the DAX does work, i just needed to be careful of what columns i have in my table, as Work Center was not part of the original DAX formula. I did add Work Center to the DAX and the results are correct.

So this begs the question how careful do you need to be at what columns are included in the SUMMARIZE statement. Is this something to be cautious of?

Cumulative Correct Reject Costs = 
VAR _CurrentOrder =
    SELECTEDVALUE ( 'Confirmations'[Order] )
VAR _CurrentOpNO =
    SELECTEDVALUE ( 'Confirmations'[Op No] )
VAR _MaxOPNo =
    MAX ( 'Confirmations'[Op No] )
VAR _CumulativeStdCosts =
    SUMX (
        CALCULATETABLE (
            SUMMARIZE (
                Confirmations,
                'Confirmations'[Activity Type],
                'Confirmations'[Op No],
                'Confirmations'[Order],
                'Confirmations'[Conf Costs]
                'Confirmations'[Work Center]
            ),
            'Confirmations'[Op No] <= _MaxOPNo,
            'Confirmations'[Order] = _CurrentOrder,
            REMOVEFILTERS ( Confirmations )
        ),
        [Std Costs Unit 1]
    )
VAR Result = [Conf Scrap Qty] * _CumulativeStdCosts
RETURN
    Result

Thanks so much @Melissa @MudassirAli and @AntrikshSharma for your assistance in solving this. :grinning:

Best Regards
J

1 Like

Hi @jprlimey,

Glad it’s resolved and big thanks for posting back your solution :+1: