Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |