Help with Sum column filter

[JRC_JDM]

I need to sum costs for all OPID's less than or equal to the current OP ID matching current job.  When in production this power BI table will contain about 100000 jobs with 15-20 operations each and other filters will be applied to make it digestable.

I've tried utilizing the Calculate, Sum, Filter, and Earlier options without success.

Example of my data from my JobOperations table, the "Total Costs to OP" column is what i'm trying to calcualte without success.

Spoiler

JOB ID	OP ID	Mat Costs	OH Costs	PO Costs	Total Costs to OP
38618	010	$8000			$8000
38618	020		$600		$8600
38618	030		$200		$8800
38618	040			$100	$8900
39152	010	$5000			$5000
39152	020		$300	$100	$5400

Thank you so much for any help.

[lc_finance · Accepted solution]

Hi @JRC_JDM ,

You can download my proposed solution from here.

I created two calculated columns, one with the sum of material, overhead and PO costs. And the second one is Total Costs to OP, which is the column you ask about.

Here are the formulas:

Material OH PO costs = 
[Mat Costs]+[OH Costs]+[PO Costs]

Total Costs to OP = 
var currentJobID = [JOB ID]
var currentOpId = [OP ID]
RETURN SUMX(FILTER('Costs OPID', 'Costs OPID'[JOB ID]=currentJobID && 'Costs OPID'[OP ID] <= currentOpId), [Material OH PO costs])

And here is a screenshot of the final result:

Is this what you are looking for?

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com